Oracle索引访问方式(二)
514164
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 1 | 4 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| XYC_INDEX | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO">8888)
PL/SQL 过程已成功完成。
（4）INDEX FAST FULL SCAN
快速完全索引扫描，不按照索引逻辑顺序读取索引数据块，而是以物理顺序读取索引
数据库（可以每次读取多个块）。示例：
[sql]
SQL> begin
2 for i in 1..10000 loop
3 insert into fast_xyc values(i,'向银春');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> create index fast_idx on fast_xyc(id);
索引已创建。
SQL> exec sql_explain('select id from fast_xyc where id>5');
Plan hash value: 1029382659
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9995 | 126K| 9 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| FAST_IDX | 9995 | 126K| 9 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">5)
Note
-----
- dynamic sampling used for this statement (level=2)
PL/SQL 过程已成功完成。
（6）INDEX FULL SCAN
全索引扫描，即对索引进行完全扫描，它与索引快速全扫描区别在于：
①：它是按照索引数据的逻辑顺序去读，而快速全扫描是按照物理存储顺序读取。
②：它每次只能读取一个数据库，而快速全扫描可以读取多个数据块。
示例：
[sql]
SQL> set pagesize 0
SQL> set lines 400
SQL> set serveroutput on
SQL> exec sql_explain('select empno from emp');
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 60 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 15 | 60 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
PL/SQL 过程已成功完成。
（7）INDEX SAMPLE FAST FULL SCAN
索引快速完全采用扫描，以多数据块和物理存储数据读取方式扫描部分数据块。示例：
[sql]
----sample(10)表示采样10%；
SQL> exec sql_explain('select id from fast_xyc sample(10) where id>5');
Plan hash value: 3595809218
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |