? ? ? ㈠ 先看兩個(gè)來(lái)自生產(chǎn)環(huán)境的真實(shí)案例：

? ? ? ? ?

? ? ? ? ?? 案例1

? ? ? ? ?? 案例2

?

? ? ? ㈡ 原理：

? ? ?
? ? ?Oracle 在對(duì)于 varchar等字符型字段收集統(tǒng)計(jì)信息時(shí)，并不會(huì)對(duì)每個(gè)值都進(jìn)行精確的統(tǒng)計(jì)
? ? ?而是，對(duì)值進(jìn)行substr(,32)。一般來(lái)講，這種方式?jīng)]有什么問(wèn)題
? ? ?但是，如果恰巧列中存儲(chǔ)的數(shù)據(jù)，前32bytes相同，那么，Oracle 的統(tǒng)計(jì)就會(huì)與實(shí)際情況不符

? ? ?
? ? ? ㈢ 測(cè)試：

?

?

    hr@ORCL> drop table t purge;



Table dropped.



hr@ORCL> create table t (id number,name varchar2(300));



Table created.



hr@ORCL> create index idx_t on t (name);



Index created.



hr@ORCL> insert into t select rownum,lpad('a',6,'a')||to_char(rownum) from dba_objects;



50322 rows created.



hr@ORCL> commit;



Commit complete.



hr@ORCL> exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for columns size 254 name',cascade=>true);



PL/SQL procedure successfully completed.



hr@ORCL> select column_name, endpoint_actual_value

  2        from user_tab_histograms

  3       where table_name = 'T'

  4             and rownum<5

  5        order by column_name, endpoint_Number;



COLUM ENDPOINT_ACTUAL_VALUE

----- --------------------------------------------------

NAME  aaaaaa46556

NAME  aaaaaa46734

NAME  aaaaaa46912

NAME  aaaaaa47090









hr@ORCL> truncate table t;



Table truncated.



hr@ORCL> insert into t select rownum,lpad('a',31,'a')||to_char(rownum) from dba_objects;



50322 rows created.



hr@ORCL> exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for columns size 254 name',cascade=>true);



PL/SQL procedure successfully completed.



hr@ORCL> select column_name, endpoint_actual_value

  2        from user_tab_histograms

  3       where table_name = 'T'

  4             and rownum<5

  5        order by column_name, endpoint_Number;



COLUM ENDPOINT_ACTUAL_VALUE

----- --------------------------------------------------

NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1

NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2

NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa3

NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4









hr@ORCL> truncate table t;



Table truncated.



hr@ORCL> insert into t select rownum,lpad('a',32,'a')||to_char(rownum) from dba_objects;



50322 rows created.



hr@ORCL> exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for columns size 254 name',cascade=>true);



PL/SQL procedure successfully completed.



hr@ORCL> select column_name, endpoint_actual_value

  2        from user_tab_histograms

  3       where table_name = 'T'

  4             and rownum<5

  5        order by column_name, endpoint_Number;



COLUM ENDPOINT_ACTUAL_VALUE

----- --------------------------------------------------

ID

ID

NAME


  

?

? ? ? ㈣ 解決方案：

? ? ?
? ? ? ? ? 刪除索引列的直方圖
? ? ? ? ? 例子：

?

?

    SQL> begin

dbms_stats.gather_table_stats(ownname => 'HR',

                              tabname => 'T' ,

                              estimate_percent => null ,

                              method_opt => 'for columns SIZE 1 name' ,

                              cascade => true);

end;

/
  

?

?

深入理解Oracle索引(10)：索引列字符類型統(tǒng)計(jì)信息的32位限制