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

点击(此处)折叠或打开

alter table card_trade_detail
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 )
using index local nologging ;

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

点击(此处)折叠或打开

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

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

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

点击(此处)折叠或打开

select * from card_trade_detail d where d.partflag >= 0 and d.partflag<= 367
and card_inner_no='0000000007735534';
执行计划
Plan Hash Value : 3925946279
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2360 | 57493 | 00:11:30 |
| 1 | PARTITION RANGE ALL | | 20 | 2360 | 57493 | 00:11:30 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | CARD_TRADE_DETAIL | 20 | 2360 | 57493 | 00:11:30 |
| * 3 | INDEX SKIP SCAN | PK_CARD_TRADE_DETAIL_LOCAL_1 | 20 | | 57157 | 00:11:26 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("D"."PARTFLAG">=0 AND "CARD_INNER_NO"='0000000007735534' AND "D"."PARTFLAG"<=367)
* 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，也是很快：

点击(此处)折叠或打开

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

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

ITPub博客

INDEX SKIP SCAN

最新文章