关于直方图（histogram）使用的一个特殊案例

有客户反映，一个选择性很好的字段（保存完整路径的文件名）filename，定义为varchar2(200)，字段的前面部分大部分是相同的（路径相同），做等值查询时（where filename='xxxxxxxx'），没有使用索引，而是使用了全表扫描。

我给出的答复是：

这是因为直方图（histogram）的存在，而直方图只取varchar2字段的前32位，如果完整路径的文件名前面32位是相同的，那么优化器将认为这个字段的NDV只有一个（虽然字段的NUM_DISTINCT可能接近NUM_ROWS，表现在dba_tab_col_statistics 视图的low_value和high_value是相同的，dba_histograms只有两条记录），就会错误的使用全表扫描的执行计划。

下面是测试用例及解决方法（数据库版本11.2）：

1、创建测试用例：

create table th (id number,filename varchar2(100),type varchar2(20));

--filename字段前32位是相同的，但是选择性还是很好的

insert into th select object_id ,rpad( 'x', 32, 'x' ) ||object_name,object_type

from dba_objects;

commit;

--创建一个filename字段上的索引：

SQL> create index idx_th_filename on th(filename) ;

exec dbms_stats.gather_table_stats(user,'th');

--第一次收集统计信息后，因为没有使用th表的sql shared pool，所以收集统计信息后是不会收集直方图的（默认值是FOR ALL COLUMNS SIZE AUTO）

SQL> select dbms_stats.get_prefs('METHOD_OPT','fred','th') from dual;

FOR ALL COLUMNS SIZE AUTO

--初始收集后，各字段都没有直方图信息

select table_name,column_name,histogram from dba_tab_col_statistics where table_name='TH';

TABLE_NAME COLUMN_NAME HISTOGRAM

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

TH TYPE NONE

TH FILENAME NONE

TH ID NONE

2、测试各种统计信息收集对SQL执行计划的影响：

--在没有直方图的情况，这个SQL的执行计划正是我们期望使用filename字段上的索引：

SQL> select /*+ run1 */* from th

where filename=rpad( 'x', 32, 'x' )||'TH' and type='TABLE';

ID FILENAME TYPE

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

114476 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxTH TABLE

--执行计划：

SQL> select * from table(dbms_xplan.display_cursor());

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

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |

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

| 0 | SELECT STATEMENT | | | | 9 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| TH | 10 | 770 | 9 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TH_FILENAME | 11 | | 3 (0)| 00:00:01 |

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

--再次收集统计信息，

SQL> exec dbms_stats.gather_table_stats(user,'TH');

--因为前面的SQL用到了filename和type两个字段，在method_opt默认为“FOR ALL COLUMNS SIZE AUTO”时，系统会自动为这两个字段收集直方图信息：

SQL> select table_name,column_name,histogram

from dba_tab_col_statistics where table_name='TH';

TABLE_NAME COLUMN_NAME HISTOGRAM

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

TH TYPE FREQUENCY

TH FILENAME FREQUENCY

TH ID NONE

--这时，SQL使用了全表扫描的执行计划，与我们之前的预期一致：

SQL> select /*+ run2 */* from th where

filename=rpad( 'x', 32, 'x' )||'TH' and type='TABLE';

ID FILENAME TYPE

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

114476 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxTH TABLE

SQL> select * from table(dbms_xplan.display_cursor());

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

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |

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

| 0 | SELECT STATEMENT | | | | 274 (100)| |

|* 1 | TABLE ACCESS FULL| TH | 3687 | 252K| 274 (1)| 00:00:01 |

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

3、解决方法：

--不收集filename字段上的直方图信息(其他字段已有的直方图信息不受影响）

SQL> exec dbms_stats.set_table_prefs('FRED','TH','METHOD_OPT','for columns filename size 1');

--再一次收集统计信息，清除filename字段上的直方图信息（模拟自动收集任务）

SQL> exec dbms_stats.gather_table_stats(user,'th');

--filename字段的histogram 变成了NONE

SQL> select table_name,column_name,histogram

from dba_tab_col_statistics where table_name='TH';

TABLE_NAME COLUMN_NAME HISTOGRAM

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

TH TYPE FREQUENCY

TH FILENAME NONE <----------------------

TH ID NONE

--再看SQL的执行计划，又变成了我们需要的index range scan：

SQL> select /*+ run3 */* from th

where filename=rpad( 'x', 32, 'x' )||'TH' and type='TABLE';

ID FILENAME TYPE

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

114476 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxTH TABLE

SQL> select * from table(dbms_xplan.display_cursor());

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

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |

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

| 0 | SELECT STATEMENT | | | | 4 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| TH | 1 | 70 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TH_FILENAME | 2 | | 3 (0)| 00:00:01 |

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

总结：

直方图信息能够帮助优化器对数据倾斜（又叫数据分布不均）字段的cardinality进行较为准确的评估（frequency 类型最佳，HEIGHT BALANCED 差一点，12c又增加了hybrid 和TOP-FREQUENCY两种类型），但是它也有局限性，本文提到的这种情况及解决方法可供参考。