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