【索引】反向索引引起排序

    反向索引是B*Tree索引的一個分支，它的設計是為了運用在某些特定的環境下的。Oracle推出它的主要目的就是為了降低在並行（Oracle Parallel Server）環境下索引葉塊的爭用。當B*Tree索引中有一列是由遞增的序列號產生的話，那麼這些索引資訊基本上分佈在同一個葉塊，當使用者修改或訪問相似的列時，索引塊很容易產生爭用。反向索引中的索引碼將會被分佈到各個索引塊中，減少了爭用。也是由於索引被散佈在不同的索引塊中，會引起不必要的排序。

SQL> create table t1 as select * from dba_objects;
Table created.
Elapsed: 00:00:00.66
SQL> create index i_id on t1(object_id);
Index created.
Elapsed: 00:00:00.28
SQL> create table t2 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.89
SQL> create index i_id_t2 on t2(object_id) reverse;
Index created.
Elapsed: 00:00:00.36

--為收集統計資訊之前。
SQL> set autot traceonly
SQL> select object_id from t2 where object_id is not null order by object_id;
53521 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 783427685
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT ORDER BY     |      |
|*  2 |   TABLE ACCESS FULL| T2   |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     975129  bytes sent via SQL*Net to client
      39740  bytes received via SQL*Net from client
       3570  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53521  rows processed

SQL>    select object_id from t1 where object_id is not null order by object_id;
53519 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2500393926
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT ORDER BY     |      |
|*  2 |   TABLE ACCESS FULL| T1   |
-----------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     974981  bytes sent via SQL*Net to client
      39729  bytes received via SQL*Net from client
       3569  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53519  rows processed

--收集統計資訊之後。
SQL> exec dbms_stats.gather_table_stats(user,'t1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
SQL> exec dbms_stats.gather_table_stats(user,'t2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
SQL> select object_id from t1 where object_id is not null order by object_id;
53519 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2369746005
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 53519 |   261K|   120   (1)| 00:00:02 |
|*  1 |  INDEX FULL SCAN | I_ID | 53519 |   261K|   120   (1)| 00:00:02 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
       3697  consistent gets
          0  physical reads
          0  redo size
     974981  bytes sent via SQL*Net to client
      39729  bytes received via SQL*Net from client
       3569  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      53519  rows processed

SQL> select object_id from t2 where object_id is not null order by object_id;
53521 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 4001069977
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 53521 |   261K|       |   197   (4)| 00:00:03 |
|   1 |  SORT ORDER BY        |         | 53521 |   261K|  1272K|   197   (4)| 00:00:03 |
|*  2 |   INDEX FAST FULL SCAN| I_ID_T2 | 53521 |   261K|       |    29   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     975129  bytes sent via SQL*Net to client
      39740  bytes received via SQL*Net from client
       3570  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53521  rows processed
---反向索引可以解決塊爭用的問題，但是如果要對結果集進行排序的話，儘量不要在該表上使用反向索引。不能在所有使用常規索引的地方使用。在範圍搜尋中其不能被使用，例如，where column>value,因為在索引的葉塊中索引碼沒有分類，所以不能透過搜尋相鄰葉塊完成區域掃描。