INDEX SKIP SCAN

1.交待一下背景：交易明细表，18亿条数据，表的容量大小是：数据量有480G，联合主键索引是112G。联合主键索引是这样建立的：

点击(此处)折叠或打开

1. alter table card_trade_detail
   
2.   add constraint pk_card_trade_detail_local_1 primary key (PARTFLAG,card_inner_no, EXTERNAL_TRADE_CODE, card_counter, TRADE_VALUE, BEFORE_TRADE_CARD_BALANCE ,TRADE_TIME )
   
3.   using index local nologging ;
   

2.几个语句的查询花去时间和执行计划让人震惊。虽然数据量很大，18亿，虽然数据量很大，表的容量有480G，索引有112G，但是仍然是0.1秒钟查询出数据，不得不说，用对了索引，效果相差很大。
这个是

点击(此处)折叠或打开

1. select * from card_trade_detail d where d.partflag >= 108 and d.partflag<= 120
   
2. and card_inner_no='0000000007735534';
   
3. 执行计划
   
4. --------------------------------------------------------------------------------------------------------------
   
5. | Id | Operation | Name | Rows | Bytes | Cost | Time |
   
6. --------------------------------------------------------------------------------------------------------------
   
7. | 0 | SELECT STATEMENT | | 1 | 118 | 4 | 00:00:01 |
   
8. | 1 | PARTITION RANGE ITERATOR | | 1 | 118 | 4 | 00:00:01 |
   
9. | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | CARD_TRADE_DETAIL | 1 | 118 | 4 | 00:00:01 |
   
10. | * 3 | INDEX RANGE SCAN | PK_CARD_TRADE_DETAIL_LOCAL_1 | 1 | | 3 | 00:00:01 |
    
11. --------------------------------------------------------------------------------------------------------------
    
12. 
    
13. Predicate Information (identified by operation id):
    
14. ------------------------------------------
    
15. * 3 - access("D"."PARTFLAG">=108 AND "CARD_INNER_NO"='0000000007735534' AND "D"."PARTFLAG"<=120)
    
16. * 3 - filter("CARD_INNER_NO"='0000000007735534')

我们来看下，上面执行计划使用了index range scan来扫描，从18亿数据中用时9秒，查出6条数据。

3.在来看下下面一个语句，使用index skip scan 从18亿数据中 用时0.189秒，查出37条数据。对，你没有看错，的确是0.189秒，不是在内存中，你换其他卡号，依然是秒查询出来。

点击(此处)折叠或打开

1. select * from card_trade_detail d where d.partflag >= 0 and d.partflag<= 367
   
2. and card_inner_no='0000000007735534';
   
3. 执行计划
   
4.  Plan Hash Value : 3925946279
   
5. 
   
6. ---------------------------------------------------------------------------------------------------------------
   
7. | Id | Operation | Name | Rows | Bytes | Cost | Time |
   
8. ---------------------------------------------------------------------------------------------------------------
   
9. | 0 | SELECT STATEMENT | | 20 | 2360 | 57493 | 00:11:30 |
   
10. | 1 | PARTITION RANGE ALL | | 20 | 2360 | 57493 | 00:11:30 |
    
11. | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | CARD_TRADE_DETAIL | 20 | 2360 | 57493 | 00:11:30 |
    
12. | * 3 | INDEX SKIP SCAN | PK_CARD_TRADE_DETAIL_LOCAL_1 | 20 | | 57157 | 00:11:26 |
    
13. ---------------------------------------------------------------------------------------------------------------
    
14. 
    
15. Predicate Information (identified by operation id):
    
16. ------------------------------------------
    
17. * 3 - access("D"."PARTFLAG">=0 AND "CARD_INNER_NO"='0000000007735534' AND "D"."PARTFLAG"<=367)
    
18. * 3 - filter("CARD_INNER_NO"='0000000007735534')

注意下的是，0,367是分区表的起始分区和结束分区。这个分区表我分了367个分区。

4.虽然索引数量很大，要知道有112G。不是说它搜索遍历了112G索引，而是应该用了INDEX SKIP SCAN这种索引算法得到结果。

5. INDEX SKIP SCAN 跳跃式扫描适合于第一个列值重复比较多 ，第二个列唯一值比较多的情况
  我们来看，我们建立了复合索引，用分区字段 PARTFLAG来做分区，每个分区有500万数据，复合 INDEX SKIP SCAN的第一个条件。而我们的语句还有一个查询条件：card_inner_no='xxxx'.所以优化器就用了 INDEX SKIP SCAN这种方式。
用hint强制数据库使用INDEX SKIP SCAN，也是很快：

点击(此处)折叠或打开

1. select/*+INDEX_SS(d PK_CARD_TRADE_DETAIL_LOCAL_1)*/ * from card_trade_detail d where d.partflag >= 108 and d.partflag<= 120
   
2. and card_inner_no='0000000007735534'

6.这还不能解释，为什么 index range scan查询需要9秒呢？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30393770/viewspace-2144423/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30393770/viewspace-2144423/