Oracle 索引访问方式
（1）INDEX UNIQUE SCAN
唯一索引扫描，唯一索引即做单一匹配。在唯一索引中，每个非空键值只有唯一的一条，主键也是唯一索引。示例：
SQL> conn scott/tiger;
SQL> set timing on
SQL> set autot trace
SQL> select * from emp where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 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"=7900)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
889 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
（2）INDEX RANGE SCAN
非唯一索引扫描，对应唯一索引扫描，索引进行范围匹配，（例如>、<、like等）或进行单一匹配（例如=），示例：
SQL> create table t_xyc as select * from emp;
Table created.
SQL> insert into t_xyc select * from emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> create index xyc_index on t_xyc(empno);
Index created.
---用等号（=）进行单一匹配
SQL> select * from t_xyc where empno=7900;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 767710755
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XYC | 2 | 174 | 3 (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"=7900)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1115 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
----用大于（>）进行范围匹配
SQL> select * from t_xyc where empno>7900;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 767710755
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 64 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_XYC | 2 | 64 | 3 (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">7900)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
（3）INDEX RANGE SCAN (MIN/MAX)
对索引进行范围扫描来获得索引字段的最大或最小值。示例：
SQL> select min(empno) from t_xyc where empno>7900;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2706514164
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| XYC_INDEX | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPNO">7900)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
（4）INDEX FAST FULL SCAN
快速完全索引扫描，不按照索引逻辑顺序读取索引数据块，而是以物理顺序读取索引数据库（可以每次读取多个块）。示例：
SQL> create table fast_xyc (id number,name varchar2(20));
Table created.
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 procedure successfully completed.
SQL> create index fast_idx on fast_xyc(id);
Index created.
SQL> set autot trace
SQL> select id from fast_xyc where id>5;
9995 rows selected.
Execution Plan
----------------------------------------------------------
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)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
732 consistent gets
21 physical reads
0 redo size
174279 bytes sent via SQL*Net to client
7849 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9995 rows processed
（6）INDEX FULL SCAN
全索引扫描，即对索引进行完全扫描，它与索引快速全扫描区别在于：
①：它是按照索引数据的逻辑顺序去读，而快速全扫描是按照物理存储顺序读取。
②：它每次只能读取一个数据块，而快速全扫描可以读取多个数据块。
示例：
SQL> select empno from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
686 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
（7）INDEX SAMPLE FAST FULL SCAN
索引快速完全采用扫描，以多数据块和物理存储数据读取方式扫描部分数据块。示例：
----sample(10)表示采样10%；
SQL> select id from fast_xyc sample(10) where id>5;
已选择996行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3595809218
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 14000 | 7 (0)| 00:00:01 |
|* 1 | INDEX SAMPLE FAST FULL SCAN| FAST_IDX | 1000 | 14000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">5)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
93 consistent gets
0 physical reads
0 redo size
17861 bytes sent via SQL*Net to client
1245 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
996 rows processed
(8)INDEX FULL SCAN (MIN/MAX)
对索引字段全扫描，以获取索引字段最大，最小值。示例：
SQL> select max(empno) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 1707959928
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| PK_EMP | 1 | 4 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
（9）INDEX FULL SCAN DESCENDING
以索引逻辑顺序相反的顺序进行完全扫描
示例：
SQL> select * from emp order by empno desc;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3088625055
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1630 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
（10）INDEX SKIP SCAN
索引跳跃扫描，在复合索引中，如果后续索引比第一索引唯一性强，而且用后续索引作为过滤条件时，会发生索引跳跃扫描。
示例：
----创建name为第一索引，但是id字段唯一性要强
SQL> create index fh_index1 on fast_xyc(name,id);
索引已创建。
SQL> var A number;
SQL> select * from fast_xyc where id=:A;
未选定行
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3991949787
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | FH_INDEX1 | 1 | 9 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=TO_NUMBER(:A))
filter("ID"=TO_NUMBER(:A))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
397 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
（11）DOMAIN INDEX
访问域索引（例如全文索引）
示例：
select * from fast_xyc where contains(id,:A)>0';
Plan hash value: 2774494995
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 460 | 19780 | 91 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| QW_XYC | 460 | 19780 | 91 (0)| 00:00:02 |
|* 2 | DOMAIN INDEX | QW_INDEX | | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("NAME",:A)>0)
Note
-----
- dynamic sampling used for this statement (level=2)
PL/SQL 过程已成功完成。
（12）BITMAP INDEX SINGLE VALUE
位图索引单值，即对一个键值访问（可以参考上边的B树索引，不举例）。
（13）BITMAP INDEX RANGE SCAN
位图范围扫描（可以参考上边的B树索引，不举例）。
（14）BITMAP INDEX FAST FULL SCAN
位图索引全扫描（可以参考上边的B树索引，不举例）。
（15）BITMAP INDEX FAST FULL SCAN
位图索引快速全扫描（可以参考上边的B树索引，不举例）。
Oracle 索引访问方式
-
不一样的天空w
2017-04-25 14:27:31 -
Oracle
- 原创