oracle 反向鍵索引的原理和用途（減少索引熱點塊）！__oracle

我們知道Oracle會自動為表的主鍵列建立索引，這個預設的索引是普通的B-Tree索引。對於主索引值是按順序(遞增或遞減)加入的情況，預設的B-Tree索引並不理想。這是因為如果索引列的值具有嚴格順序時，隨著資料行的插入，索引樹的層級增長很快。搜尋索引發生的I/O讀寫次數和索引樹的層級數成正比，也就是說，一棵具有5個層級的B -Tree索引，在最終讀取到索引資料時最多可能發生多達5次I/O操作。因而，減少索引的層級數是索引效能調整的一個重要方法。

如果索引列的資料以嚴格的有序的方式插入，那麼B-Tree索引樹將變成一棵不對稱的"歪樹"，如圖 5所示：

而如果索引列的資料以隨機值的方式插入，我們將得到一棵趨向對稱的索引樹，如圖 6所示：

比較圖 5和圖 6，在圖 5中搜尋到A塊需要進行5次I/O操作，而圖 6僅需要3次I/O操作。
既然索引列資料從序列中擷取，其有序性無法規避，但在建立索引時，Oracle允許對索引列的值進行反向，即預先對列值進行位元位的反向，如 1000,10001,10011,10111,1100經過反向後的值將是0001,1001,1101,0011。顯然經過位反向處理的有序資料變得比較隨機了，這樣所得到的索引樹就比較對稱，從而提高表的查詢效能。
但反向鍵索引也有它局限性：如果在WHERE語句中，需要對索引列的值進行範圍性的搜尋，如BETWEEN、<、>等，其反向鍵索引無法使用，此時，Oracle將執行全表掃描；只有對反向鍵索引列進行 <>和 = 的比較操作時，其反向鍵索引才會得到使用。

1.反向索引應用場合
1）發現索引葉塊成為熱點塊時使用
通常，使用資料時（常見於批量插入操作）都比較集中在一個連續的資料範圍內，那麼在使用正常的索引時就很容易發生索引葉子塊過熱的現象，嚴重時將會導致系統效能下降。
2）在RAC環境中使用
當RAC環境中幾個節點訪問資料的特點是集中和密集，索引熱點塊發生的幾率就會很高。如果系統對範圍檢索要求不是很高的情況下可以考慮使用反向索引技術來提高系統的效能。因此該技術多見於RAC環境，它可以顯著的降低索引塊的爭用。

2.使用反向索引的優點
最大的優點莫過於降低索引葉子塊的爭用，減少熱點塊，提高系統效能。

3.使用反向索引的缺點
由於反向索引結構自身的特點，如果系統中經常使用範圍掃描進行讀取資料的話（例如在where子句中使用“between and”語句或比較子“>”“<”等），那麼反向索引將不適用，因為此時會出現大量的全表掃描的現象，反而會降低系統的效能。

有時候可以通過改寫sql語句來避免使用範圍掃描，例如where id between 12345 and 12347，可以改寫為where id in(12345,12346,12347)，CBO會把這樣的sql查詢轉換為where id=12345 or id=12346 or id=12347,這對反向索引也是有效。
4.通過一個小實驗簡單示範一下反向索引的建立及修改

SQL> select count(*) from t1;  COUNT(*)----------         0SQL> select count(*) from t2;  COUNT(*)----------         0SQL> select count(*) from t3;  COUNT(*)----------   2000000SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes;INDEX_NAME                     INDEX_TYPE                  TABLE_NAME------------------------------ --------------------------- ------------------------------PK_T2                          NORMAL/REV                  T2PK_T1                          NORMAL                      T1

表t1是主鍵是正常的主鍵，表t2的主鍵是反向主鍵。現在我把表t3的資料分別插入到表t1和表t2

SQL> set timing on;SQL> set autotrace on;SQL> insert /* +append */ into t1 select * from t3;已建立2000000行。經過時間:  00: 01: 42.83執行計畫----------------------------------------------------------Plan hash value: 4161002650---------------------------------------------------------------------------------| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | INSERT STATEMENT         |      |  2316K|   485M| 19014   (1)| 00:03:49 ||   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          ||   2 |   TABLE ACCESS FULL      | T3   |  2316K|   485M| 19014   (1)| 00:03:49 |---------------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)統計資訊----------------------------------------------------------      12305  recursive calls     538835  db block gets     203937  consistent gets      83057  physical reads  428323528  redo size        688  bytes sent via SQL*Net to client        614  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          2  sorts (memory)          0  sorts (disk)    2000000  rows processedSQL> commit;提交完成。經過時間:  00: 00: 00.04SQL> insert /* +append */ into t2 select * from t3;已建立2000000行。經過時間:  00: 02: 02.63執行計畫----------------------------------------------------------Plan hash value: 4161002650---------------------------------------------------------------------------------| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | INSERT STATEMENT         |      |  2316K|   485M| 19014   (1)| 00:03:49 ||   1 |  LOAD TABLE CONVENTIONAL | T2   |       |       |            |          ||   2 |   TABLE ACCESS FULL      | T3   |  2316K|   485M| 19014   (1)| 00:03:49 |---------------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)統計資訊----------------------------------------------------------       7936  recursive calls    6059147  db block gets     158053  consistent gets      56613  physical reads  790167468  redo size        689  bytes sent via SQL*Net to client        614  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          2  sorts (memory)          0  sorts (disk)    2000000  rows processedSQL> commit;提交完成。經過時間:  00: 00: 00.01

可以看見：由於反向索引的資料區塊比較分散了後，db block gets要稍微高一些。熱塊的爭用有所緩解，consistent gets有所下降，從203937下降到158053，減少了45884次。redo size 也變多了。再來做查詢，來看看他們的區別。

SQL> set autotrace traceonly;SQL> select OBJECT_NAME from t1 where id = 100;經過時間:  00: 00: 00.06執行計畫----------------------------------------------------------Plan hash value: 1141790563-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     1 |    79 |     0   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    79 |     0   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | PK_T1 |     1 |       |     0   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=100)統計資訊----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          3  physical reads          0  redo size        434  bytes sent via SQL*Net to client        416  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> select OBJECT_NAME from t1 where id > 100 and id < 200;已選擇99行。經過時間:  00: 00: 01.10執行計畫----------------------------------------------------------Plan hash value: 1249713949-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |    99 |  7821 |     1   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    99 |  7821 |     1   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | PK_T1 |    99 |       |     1   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID">100 AND "ID"<200)Note-----   - dynamic sampling used for this statement (level=2)統計資訊----------------------------------------------------------          9  recursive calls          0  db block gets        140  consistent gets        189  physical reads       2356  redo size       2656  bytes sent via SQL*Net to client        482  bytes received via SQL*Net from client          8  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         99  rows processedSQL> select OBJECT_NAME from t2 where id = 100;經過時間:  00: 00: 00.05執行計畫----------------------------------------------------------Plan hash value: 1480579010-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     1 |    79 |     0   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    79 |     0   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | PK_T2 |     1 |       |     0   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=100)統計資訊----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          1  physical reads          0  redo size        434  bytes sent via SQL*Net to client        416  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL> select OBJECT_NAME from t2 where id > 100 and id < 200;已選擇99行。經過時間:  00: 00: 04.39執行計畫----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |   336 | 26544 |  8282   (1)| 00:01:40 ||*  1 |  TABLE ACCESS FULL| T2   |   336 | 26544 |  8282   (1)| 00:01:40 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ID">100 AND "ID"<200)Note-----   - dynamic sampling used for this statement (level=2)統計資訊----------------------------------------------------------         29  recursive calls          1  db block gets      60187  consistent gets      30335  physical reads       5144  redo size       2656  bytes sent via SQL*Net to client        482  bytes received via SQL*Net from client          8  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         99  rows processed

可以看見，單個值查詢的時候，表t1和表t2並無差別，但是範圍查詢的時候，表t1是INDEX RANGE SCAN，表t2是TABLE ACCESS FULL了。在資料庫的最佳化中你經常會發現沒有絕對的好，也沒有絕對的差。

在考慮使用反向索引之前，大多數情況可以考慮對索引進行散列分區（hash）來減少索引葉塊的爭用。

反向索引：
 alter index id_inx rebuild reverse online;
 alter index id_inx rebuild online reverse;
 alter index name_inx rebuild online noreverse;