Oracle索引访问方式
显示执行计划的存储过程请参考
http://www.zzzyk.com/database/201303/192688.html
（1）INDEX UNIQUE SCAN
唯一索引扫描，唯一索引即做单一匹配。在唯一索引中，每个非空键值只有唯一的一条，主键也是唯一索引。示例： www.zzzyk.com
[sql]
SQL> exec sql_explain('select * from emp where empno=8888');
Plan hash value: 2949544139
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)|
00:00:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=8888)
PL/SQL 过程已成功完成。
（2）INDEX RANGE SCAN
非唯一索引扫描，对应唯一索引扫描，索引进行范围匹配，（例如>、<、like等）或进行单一匹配（例如=），示例：
[sql]
SQL> create table t_xyc as select * from emp;
表已创建。
SQL> insert into t_xyc select * from emp;
已创建15行。
SQL> commit;
提交完成。
SQL> create index xyc_index on t_xyc(empno);
索引已创建。
---用等号（=）进行单一匹配
SQL> exec sql_explain('select * from t_xyc where empno=8888');
Plan hash value: 767710755
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XYC | 2 | 78 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | XYC_INDEX | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=8888)
PL/SQL 过程已成功完成。
----用大于（>）进行范围匹配
SQL> exec sql_explain('select * from t_xyc where empno>8888');
Plan hash value: 767710755
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XYC | 2 | 78 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | XYC_INDEX | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO">8888)
PL/SQL 过程已成功完成。
（3）INDEX RANGE SCAN (MIN/MAX)
对索引进行范围扫描来获得索引字段的最大或最小值。示例：
[sql]
SQL> exec sql_explain('select min(empno) from t_xyc where empno>8888');
Plan hash value: 2706514164
------------------------------------------------------------------------------------------
| 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;
