Oracle索引扫描的4种类型(二)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:
|* 2 | INDEX RANGE SCAN | INDEX_T | 1 | | 1 (0)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=75780)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
37 recursive calls
0 db block gets
118 consistent gets
1 physical reads
0 redo size
1444 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
三. 索引全扫描(index full scan ) 索引快速扫描(index fast full scan)
索引全扫描(index full scan )
与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
例子:
SQL> create table t3 as select * from dba_objects where object_id is not null;
表已创建。
SQL> alter table t3 modify(object_id not null);
表已更改。
SQL> create index index_t3 on t3(object_id);
索引已创建。
SQL> set autotrace trraceonly;
SQL> select /*+index(t index_t3) */ object_id from t3 t;
已选择114164行。
执行计划
----------------------------------------------------------
Plan hash value: 3077935993
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 1451K| 270 (1)| 00:00:04 |
| 1 | INDEX FULL SCAN | INDEX_T3 | 114K| 1451K| 270 (1)| 00:00:04 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
7916 consistent gets
0 physical reads
0 redo size
1663703 bytes sent via SQL*Net to client
84126 bytes received via SQL*Net from client
7612 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
114164 rows processed
索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，与 index full scan很类似，在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。
SQL> select object_id from t3;
已选择114164行。
执行计划
----------------------------------------------------------
Plan hash value: 448706225
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1