SQL> set autot on
SQL> select rowid, object_id
2 from test
3 where rowid ='AAARVKAAEAAAAD8AAA';
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2153624467
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY USER ROWID| TEST |
-------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意到统计信息，只有一个consistent gets，只需要一次读取即可以获得数据。这种方式是访问数据最快的方式。
----当查询条件无法命中任何索引或者扫描索引的代价大于全表扫描代价的某一比率(optimizer_index_cost_adj)的时候,oracle会选择使用全表扫描。此时oracle 会读取一定数量的数据块，直到hwm。
SQL> select rowid ,object_id
2 from test
3 where rownum =1;
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2347100821
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS FULL| TEST |--全表扫描。
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------
SQL> select rowid ,object_id from test where rowid = 'AAARVKAAEAAAAD8AAA';
ROWID OBJECT_ID
------------------ ----------
AAARVKAAEAAAAD8AAA 28
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2153624467
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| TEST | 1 | 17 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
147 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
。。。
3 sorts (memory)
0 sorts (disk)
1 rows processed
-----------------------------------------------
----建立索引
SQL> create table un_test tablespace users as select * from dba_objects;
Table created.
Elapsed: 00:00:02.18
SQL> create unique index i_test_uni on un_test (object_id);
Index created.
Elapsed: 00:00:00.24
---INDEX UNIQUE SCAN 当索引为unique时,每个子叶节点只会指向一条数据.
如果oracle预测到只有0或1条数据时,就会选择INDEX UNIQUE SCAN .
SQL> select object_id from un_test where object_id =28;
OBJECT_ID
----------
28
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 333614268
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| I_TEST_UNI | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - access("OBJECT_ID"=28)
---INDEX RANGE SCAN
当通过索引查找数据时，oracle 认为会返回数据可能大于1 ，会进行INDEX RANGE SCAN.
例如进行范围查询， ,LIKE,BETWEEN VAL1 AND VAL2 ,IN 等操作。
INDEX RANGE SCAN， INDEX UNIQUE SCAN 会引起db file sequential read 等待
SQL> select object_id from un_test where object_id >5 and object_id 9;
OBJECT_ID
----------
6
7
8
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1694266620
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TEST_UNI | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID">5 AND "OBJECT_ID"Statistics
----------------------------------------------------------
--INDEX FAST FULL SCAN
索引快速扫描和全表扫描类似，一次读取db_file_multiblock_count 个数据块。INDEX FAST FULL SCAN
与其他索引扫描不同，它不会从根节点开始，也不读取节点，而是直接扫描所有子叶节点;也不会一次读取一个数据块。
SQL> select object_id from un_test where object_id >3;
53362 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 972078537
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53364 | 260K| 27 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| I_TEST_UNI | 53364 | 260K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3670 consistent gets
0 physical reads
0 redo size
。。。。。。。
0 sorts (memory)
0 sorts (disk)
53362 rows processed
SQL> select object_id from un_test where object_id>500;
52887 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 972078537
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52998 | 258K| 27 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| I_TEST_UNI | 52998 | 258K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">500
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3634 consistent gets
0 physical reads
0 redo size
。。。。。。。。。
0 sorts (memory)
0 sorts (disk)
52887 rows processed
有时如果 的值过小，强制使用索引扫描时，会发生INDEX FULL SCAN .INDEX FULL SCAN 和 INDEX FAST FULL SCAN 不同。它是一种索引扫描，从根节点开始扫描，遍历整棵索引树，并且一次读取一个索引块，IFS 会引起 db file sequential read 事件。