有客户反映，一个选择性很好的字段（保存完整路径的文件名）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字段上的索引：
create index idx_th_filename on th(filename) ;
--第一次收集统计信息后，因为没有使用th表的sql shared pool，所以收集统计信息后是不会收集直方图的（默认值是FOR ALL COLUMNS SIZE AUTO）
exec dbms_stats.gather_table_stats(user,'th');
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两种类型），但是它也有局限性，本文提到的这种情况及解决方法可供参考。
