㈠ 先看两个来自生产环境的真实案例：

          案例1

          案例2

     ㈡ 原理：

     
     Oracle 在对于 varchar等字符型字段收集统计信息时，并不会对每个值都进行精确的统计
     而是，对值进行substr(,32)。一般来讲，这种方式没有什么问题
     但是，如果恰巧列中存储的数据，前32bytes相同，那么，Oracle 的统计就会与实际情况不符

     
     ㈢ 测试：

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;
/