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秒呢？

INDEX SKIP SCAN

相關文章