Oracle 索引访问方式

本篇记录一些索引访问操作

表访问方式请参考：http://blog.csdn.net/laoshangxyc/article/details/8630540

显示执行计划的存储过程请参考

（1）INDEX UNIQUE SCAN

唯一索引扫描，唯一索引即做单一匹配。在唯一索引中，每个非空键值只有唯一的一条，主键也是唯一索引。示例：

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 过程已成功完成。

非唯一索引扫描，对应唯一索引扫描，索引进行范围匹配，（例如>、<、like等）或进行单一匹配（例如=），示例：

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> 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("EMPN