深入理解Oracle索引（5）：反向索引的定義、缺點和適用情境

        ㈠ 定義
          
          建立一個反向索引將把每個列的索引值(each column key value)按位元組反向過來，對於按鍵組合，列的順序被保留，但每個列的位元組都作了反向
          例如：
          表的某一列內容
          ……
          1234
          1235
          1236
          1237
          ……
          
          建立正向索引
          ……
          1234
          1235
          1236
          1237
          ……
          這四行放在同一個leaf block中。
          如果事務A查詢1234這行，同時事務B查詢1235這行。那麼就會在這個leaf block上發生I/O爭用
          
          
          建立反向索引
          ……
          4321
          5321
          6321
          7321
          ……
          這四行放在四個不同leaf block中
          如果事務A查詢1234這行，同時事務B查詢1235這行。是分別在兩個leaf block上進行，不會發生I/O爭用
          
          很多事務訪問同一個塊，對同一個塊並行作業產生的I/0競爭
          反向索引能作為避免熱點塊的一個方法
          
       ㈡ 尋找
          

          user_indexes.index_type

scott@ORCL> create index idx_rev on emp(sal) reverse;Index created.scott@ORCL> select index_name,index_type from user_indexes where index_name='IDX_REV';INDEX_NAME                     INDEX_TYPE------------------------------ ---------------------------IDX_REV                        NORMAL/REV

       ㈢ 它有什麼缺點？
          
          ① if you use reverse key index，index range scan will not work
          ② 當應用需要擷取一段範圍的資料時，reverse key index將不會被使用，因為索引值不是連續的排列的。在這種情況下，CBO將會選擇全表掃描
          
          測試：

hr@ORCL> drop table t purge;Table dropped.hr@ORCL> create table t (a number,b varchar2(20));Table created.hr@ORCL> ed   Wrote file afiedt.buf  1  begin  2    for i in 1..20000  3    loop  4      insert into t values(i,to_char(sysdate,'yyyymmddhhmmss'));  5      commit;  6    end loop;  7* end;hr@ORCL> /PL/SQL procedure successfully completed.hr@ORCL> create index idx_t on t (a) reverse;Index created.hr@ORCL> set autot on exphr@ORCL> select * from t where a >=19989 and a <=19990;         A B---------- --------------------     19989 20130224060219     19990 20130224060219Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     2 |    50 |    19   (6)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T    |     2 |    50 |    19   (6)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("A">=19989 AND "A"<=19990)Note-----   - dynamic sampling used for this statementhr@ORCL> drop index idx_t;Index dropped.hr@ORCL> create index idx_t on t (a);Index created.hr@ORCL> analyze index idx_t compute statistics;Index analyzed.hr@ORCL> select * from t where a >=19989 and a <=19990;         A B---------- --------------------     19989 20130224060219     19990 20130224060219Execution Plan----------------------------------------------------------Plan hash value: 1594971208-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     2 |    50 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |    50 |     3   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T |     2 |       |     2   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A">=19989 AND "A"<=19990)Note-----   - dynamic sampling used for this statement

       ㈣ 什麼時候使用它？
          
          反向索引主要是建立在那些以序號產生的列上，可以將本來是連在一起的index entry分散到不同的leaf block中去
          當索引是從序列中取的時候，如果是一般的b-tree 索引，在大量的插入後會導致塊的分裂以及樹的傾斜，使用reverse key index可以使索引段條目被更均勻的分布
          
          
       所以，reverse index主要是緩解右向增長的索引右側葉子節點的爭用,對於查詢意義不大,注意reverse索引可能導致無法走range scan
       但用於解決被索引引起的熱塊問題倒是很不錯的！