前导列基数对索引跳跃式扫描的影响

【创建测试表】

 

DROP TABLE t PURGE;
create table t TABLESPACE dict_tbs as select * from all_objects ;

--建立索引（前导列基数低）
create index t_index1 on t(owner,object_name);

--收集统计信息
BEGIN
  dbms_stats.gather_table_stats(ownname          => USER,
                                tabname          => 't',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                cascade          => TRUE);
END;
/

--查看统计信息
SELECT utcs.num_distinct
FROM   user_tab_col_statistics utcs
WHERE  utcs.table_name = 'T' AND
       utcs.column_name IN('OWNER','OBJECT_NAME');
--24 29627

SELECT data_object_id, created FROM t WHERE object_name = 'DBA_EXTENTS';

 

【创建前导列基数相对较高的列】

 

drop index t_index1;
create index t_index1 on t (object_name,owner);

--收集统计信息
SELECT * FROM t WHERE owner = 'SCOTT';
BEGIN
  dbms_stats.set_column_stats(ownname       => USER,
                              tabname       => 't',
                              colname       => 'object_name',
                              distcnt       => 2,
                              no_invalidate => FALSE);
  dbms_stats.set_column_stats(ownname       => USER,
                              tabname       => 't',
                              colname       => 'owner',
                              distcnt       => 56660,
                              no_invalidate => FALSE);
END;
/

SELECT * FROM t WHERE owner = 'SCOTT';

 

  【结论】

 
网上说索引跳跃式扫描的机制，其实是将原sql重写成使用前导列的union
语句，但是我从10046和10053跟踪文件中都未找到，从实验结果看，似乎是这样的。