深入理解Oracle索引(10)：索引列字符类型统计信息的32位限制

来源：互联网发布：淘宝微信营销方案编辑：程序博客网时间：2024/06/10 19:37

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

案例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  aaaaaa46556NAME  aaaaaa46734NAME  aaaaaa46912NAME  aaaaaa47090hr@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  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa3NAME  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4hr@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----- --------------------------------------------------IDIDNAME

㈣解决方案：

删除索引列的直方图
例子：

SQL> begindbms_stats.gather_table_stats(ownname => 'HR',                              tabname => 'T' ,                              estimate_percent => null ,                              method_opt => 'for columns SIZE 1 name' ,                              cascade => true);end;/