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索引
- web方式訪問sshWeb
- Oracle外網訪問Oracle
- Oracle 訪問路徑Oracle
- grpc提供http訪問方式RPCHTTP
- 訪問外部裝置方式
- Oracle 索引Oracle索引
- Holer實現Oracle外網訪問Oracle
- Oracle資料庫限制訪問IPOracle資料庫
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- mariadb配置允許遠端訪問方式
- oracle的索引Oracle索引
- 如何限制ip訪問Oracle資料庫Oracle資料庫
- Oracle/MySQL透過odbc訪問PostgreSQL for LightDBOracleMySql
- 【TUNE_ORACLE】列出可以建立組合索引的SQL(回表訪問少數字段)的SQL參考Oracle索引SQL
- Oracle一次“選錯索引”問題的分析Oracle索引
- NumPy 陣列建立方法與索引訪問詳解陣列索引
- js如何使用索引訪問陣列物件中的元素JS索引陣列物件
- 資料庫訪問幾種方式對比資料庫
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- oracle重建索引（一）Oracle索引
- oracle重建索引（三）Oracle索引
- 0707_oracle 索引Oracle索引
- oracle重建索引（二）Oracle索引
- Oracle OCP(25)：索引Oracle索引
- Oracle虛擬索引Oracle索引
- Oracle：全文索引Oracle索引
- 【廖雪峰python入門筆記】list_按照索引訪問Python筆記索引
- LightDB/postgresql內建特性之訪問oracle之oracle_fdw介紹SQLOracle
- kubernetes使用traefik的https方式訪問web應用HTTPWeb
- Struts2教程之三Action訪問方式
- NAS儲存外網遠端訪問的方式
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- Oracle RAC Cache Fusion 系列十三：PCM資源訪問Oracle
- Oracle資料訪問元件ODAC的安裝方法Oracle元件
- 透過Kerberos認證訪問Oracle11gROSOracle