【索引】反向索引--條件 範圍查詢

---檢視索引的型別
SQL> col table_name for a10
SQL> col index_name foa a10
SQL> select table_name ,index_name ,index_type from user_indexes where table_name in ('T1','T2');
TABLE_NAME INDEX_NAME                     INDEX_TYPE
---------- ------------------------------ ---------------------------
T2         I_ID_T2                        NORMAL/REV  ---反向索引
T1         I_ID                           NORMAL      ---常規索引

Elapsed: 00:00:00.08
---手動轉變索引的型別
SQL> alter index i_id rebuild reverse;  ---將常規索引轉變為反向索引
Index altered.
Elapsed: 00:00:00.36
SQL> alter index i_id_t2 rebuild noreverse;---將反向索引轉變為常規索引
Index altered.
Elapsed: 00:00:00.30
SQL> select table_name ,index_name ,index_type from user_indexes where table_name in ('T1','T2');
TABLE_NAME INDEX_NAME                     INDEX_TYPE
---------- ------------------------------ ---------------------------
T2         I_ID_T2                        NORMAL
T1         I_ID                           NORMAL/REV
Elapsed: 00:00:00.00
---檢視反向索引對於範圍查詢和比較 < > 條件查詢的執行計劃 表t1 是反向索引，走iffs 原因：因為這裡select 的欄位是索引欄位，所以走了iffs。
SQL> set autot traceonly
SQL> select object_id from t1 where object_id between 2 and 50;
49 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 711836071
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    15 |    75 |    29   (4)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I_ID |    15 |    75 |    29   (4)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<=50 AND "OBJECT_ID">=2)
Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
        132  consistent gets
        118  physical reads
           .
           .
           .
         49  rows processed

Elapsed: 00:00:00.00
--表t2 走range scan  常規索引
SQL> select object_id from t2 where object_id between 2 and 50;
49 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3216146664
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |    26 |   130 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_ID_T2 |    26 |   130 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID">=2 AND "OBJECT_ID"<=50)
Statistics
----------------------------------------------------------
         15  recursive calls
           .
           .
           .
         49  rows processed
-----全表掃描，由於select * 選擇所有的欄位資訊，object_id  索引裡面的資料不能滿足要求，所以走了全表掃描，當然，更重要的原因是因為索引項在索引塊中的雜亂排序。

SQL> select * from t1 where object_id between 2 and 50;
49 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 838529891
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |  1395 |   164   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |    15 |  1395 |   164   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"<=50 AND "OBJECT_ID">=2)
Statistics
----------------------------------------------------------
        244  recursive calls
          0  db block gets
        778  consistent gets
          0  physical reads
          0  redo size
       3278  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  row