查询条件包含组合索引所有键为啥执行计划走的是index skip scan？？？

feiweiwei1985

最近在生产环境发现一支交易的执行计划走的是index skip scan，语句为 select * from inct where master_ac_no='123456789012' and trn_date>=to_date('20110801','yyyymmdd') and trn_date<=to_date('20111001','yyyymmdd'); 其中inct表为交易表，数据量为30亿多，一级分区为trn_date（交易时间）的按月range分区，二级分区为master_ac_no（账号）的hash分区，索引为local index索引键为trn_date、master_ac_no组合索引。这里对于这样的语句where条件里有组合索引的所有键值，执行计划应该是index range scan 但是为什么走的却是index skip scan，查看了执行计划逻辑读比较高，求高手解答，问题困惑了几天了找不到原因。。。

xifenfei

跨分区导致？
明天去公司测试下

feiweiwei1985

有别的类似的交易表就没有这样的情况，走的都是index range scan，试过了没跨月分区也会走index skip scan，很诡异

feiweiwei1985

这个应该不是跨分区导致的，这种分区方式的应用在银行或者证券系统应该是挺常见的

xccheese

ORACLE官方说，在前导列唯一值较少的情况下，才会用到index skip can。
master_ac_no='123456789012' 这个是不是组合索引的前导列？逻辑值比较高，看看你的索引深度或rebuilt一下索引试试。

nmgzw

1. 请参考！这个没有什么原因能解释，只能说是oracle一个bug!
   
2. 参照CBO一书原文如下！
   
3. 
   
4. create index t1_i1 on t1(n1, ind_pad, n2)
   
5. 
   
6. More on Range-based Tests
   
7. We took the easy option, and did a range-based test on the last column in the index. What
   
8. happens if we do a range-based test on an earlier column in the index? Try this, for example:
   
9. alter session set "_optimizer_skip_scan_enabled"=false;
   
10. select
    
11. /*+ index(t1) */
    
12. small_vc
    
13. from
    
14. t1
    
15. where
    
16. n1 between 1 and 3
    
17. and ind_pad = rpad('x',40)
    
18. and n2 = 2
    
19. ;
    
20. Execution Plan (8.1.7.4)
    
21. ----------------------------------------------------------
    
22. 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=82 Bytes=4756)
    
23. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=82 Bytes=4756)
    
24. 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=82)
    
25. Execution Plan (9.2.0.6 and 10.1.0.4)
    
26. ----------------------------------------------------------
    
27. 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=82 Bytes=4756)
    
28. 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=82 Bytes=4756)
    
29. 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=1633)
    
30. 
    
31. The alter session command is there for the benefit of 9i and 10g. The unhinted execution
    
32. plan was a full tablescan, but when I first put in an index hint, the optimizer insisted on using
    
33. the index skip scan mechanism, and in 10g if I then included the no_index_ss() hint, the index
    
34. was disabled and the plan went back to a tablescan (I would be inclined to call this behavior a
    
35. bug—it seems perfectly reasonable to me to say, “Use this index, but don’t do a skip scan,” but
    
36. it is possible that it’s the specified behavior).

复制代码

ncgxq

统计值不全或某些bug也会导致类似现象发生，应用是否使用了绑定变量？语句是否有多个执行计划，使用select * from table(dbms_xplan.display_awr('sqlid'));看一下是不是有多个plan，如果是的话，可能是由于bind peaking特性导致，可以grant select on xxx to public再revoke重新生成执行计划，不过如果业务比较忙的时候可能还是不顶用的。

feiweiwei1985

nmgzw 发表于 2011-10-10 08:25

查了一些资料也有人提到说10g CBO比较倾向于index skip scan是一个bug，说是在11g已经解决了，但是我现在还不能在生产上直接把index skip scan参数调整为false，因为其他有的地方会用到，之前也试过在这句sql上加no_index_ss，效果和你描述的一样，CBO会选择走table full scan而不是走index range scan，不知道有没有其他方法可以解决这个问题。

feiweiwei1985

xccheese 发表于 2011-10-9 23:43
ORACLE官方说，在前导列唯一值较少的情况下，才会用到index skip can。
master_ac_no='123456789012' 这个 ...

我这个表里的前导列是trn_date，索引的blevel是2，trn_date的选择性较低是按月做的分区，master_ac_no有较高的选择性，官方的说法是如果在where条件中没有trn_date的只有master_ac_no的情况下才会走index skip scan，rebuild过最近两个月的分区，然后只是查询这两个月的交易还是走的是skip scan。

棉花糖ONE

select /*+ index_rs(inct) */ * from inct where master_ac_no='123456789012' and trn_date>=to_date('20110801','yyyymmdd') and trn_date<=to_date('20111001','yyyymmdd');

看看