【索引】反向索引--条件 范围查询

---查看索引的类型
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-672884/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-672884/