Oracle 索引訪問方式
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樹索引，不舉例）。
（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樹索引，不舉例）。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2137938/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 索引訪問的幾種方式Oracle索引
- 常見SQL訪問索引的方式SQL索引
- Oracle 表訪問方式Oracle
- oracle表訪問方式Oracle
- Oracle指令碼(Oracle Scripts) – 檢視索引訪問次數及索引訪問型別Oracle指令碼索引型別
- Oracle訪問表的方式Oracle
- Oracle訪問索引的執行計劃（一）Oracle索引
- Oracle訪問索引的執行計劃（二）Oracle索引
- Oracle訪問索引的執行計劃（三）Oracle索引
- Oracle訪問索引的執行計劃（四）Oracle索引
- Oracle訪問索引的執行計劃（五）Oracle索引
- 執行計劃-資料訪問方式（全表掃描與4種索引的方式）索引
- 【Oracle】 索引的掃描方式Oracle索引
- Python訪問Oracle的兩種資料獲取方式PythonOracle
- grpc提供http訪問方式RPCHTTP
- for迴圈訪問php的索引陣列PHP索引陣列
- ruby 訪問新浪微博API post方式和get方式API
- c#索引訪問器再探_get_set之訪問控制存取C#索引
- Oracle 訪問路徑Oracle
- Oracle外網訪問Oracle
- mono 訪問 oracle、mysqlMonoOracleMySql
- oracle 限定ip訪問Oracle
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- JVM中物件訪問定位兩種方式JVM物件
- Oracle EBS DMZ區訪問問題Oracle
- 用Python訪問OraclePythonOracle
- 【執行計劃】資料訪問方式,連線方式及方法
- mariadb配置允許遠端訪問方式
- smarty中三種變數的訪問方式變數
- 兩種訪問介面的方式(get和post)
- Oracle 索引的三個問題(轉)Oracle索引
- js如何使用索引訪問陣列物件中的元素JS索引陣列物件
- 從Oracle訪問SQL Server（GATEWAYS）OracleSQLServerGateway
- ORACLE 訪問MYSQL 配置筆記OracleMySql筆記
- Oracle 單表訪問路徑Oracle
- .net之oraclecommand訪問oracleOracle
- Oracle資料庫訪問控制Oracle資料庫
- 通過Oracle Gateways 訪問SybaseOracleGateway