1、TABLE ACCESS BY INDEX ROWID表示回表，單塊讀;

2、INDEX UNIQUE SCAN 表示索引唯一掃描，單塊讀;

對唯一索引或者對主鍵列進行等值查詢，就會走INDEX UNIQUE SCAN，因為對唯一索引或者對主鍵列進行等值查詢，CBO能確保最多隻返回1行資料，所以這時可以走索引唯一掃描。

INDEX UNIQUE SCAN最多隻返回一行資料，只會掃描“索引高度”個索引塊，在所有的Oracle訪問路徑中，其效能僅次於TABLE ACCESS BY USER ROWID。

SQL&get; select * from emp where empno=7369;

Execution Plan

----------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 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"=7369)

因為empno是主鍵列，對empno進行等值訪問，就走了INDEX UNIQUE SCAN

3、INDEX RANGE SCAN表示索引範圍掃描，單塊讀，返回的資料是有序的(預設升序)。HINT: INDEX(表名/別名索引名)。對唯一索引或者主鍵進行範圍查詢，對非唯一索引進行等值查詢，範圍查詢，就會發生INDEX RANGE SCAN。等待事件為db file sequential read。

SQL&get; select * from test where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3946039639

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=100)

索引範圍掃描預設是從索引中最左邊的葉子塊開始，然後往右邊的葉子塊掃描(從小到大)，當檢查到不匹配的資料的時候，就停止掃描。現在將過濾條件改為小於，並且對過濾列進行降序排序，

檢視執行計劃：

SQL&get; select * from test where object_id<100 order by object_id desc;

98 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1069979465

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 96 | 9312 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 96 | 9312 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN DESCENDING| IDX_ID | 96 | | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<100)

filter("OBJECT_ID"<100)

INDEX RANGE SCAN DECENDING表示索引降序範圍掃描，從右往左掃描，返回的資料是降序顯示的。

結論：

假設一個索引葉子塊能儲存100行資料，透過索引返回100行以內的資料只掃描“索引高度”這麼多個索引塊，如果透過索引返回200行資料，就需要掃描2個葉子塊，透過索引返回的行數越多，

掃描的索引葉子塊也就越多，隨著掃描的葉子塊個數的增加，索引範圍掃描的效能開銷也就越大。

如果索引範圍掃描需要回表，同樣假設一個索引葉子塊能儲存100行資料，如果透過索引返回1000行資料，

只需要掃描10個索引葉子塊(10個單塊讀)，但是回表可能會需要訪問幾十個到幾百個表塊(幾十到幾百個單塊讀)。

在檢查執行計劃的時候要注意索引範圍掃描返回多少行資料，如果返回少量資料，不會出現效能問題，如果返回大量資料，在沒有回表的情況下也還好，

如果返回大量資料，還有回表，這時應該考慮透過建立組合索引消除回表或者使用全表掃描代替。

4、INDEX SKIP SCAN表示索引跳躍掃描，單塊讀。返回的資料是有序的(預設升序)。

HINT: INDEX_SS(表名/別名索引名)。當組合索引的引導列(第一個列)沒有在where條件中，並且組合索引的引導列/前幾個列的基數很低，where過濾條件對組合索引中非引導列進行過濾的時候就會發生索引跳躍掃描，等待事件為db file sequential read。

在測試表test上建立如下索引：

SQL&get; create index idx_ownerid on test(owner,object_id);

Index created.

刪除object_id列上的索引IDX_ID：

SQL&get; drop index idx_id;

Index dropped.

執行如下SQL並且檢視執行計劃：

SQL&get; select * from test where object_id<100;

98 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 847134193

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 96 | 9312 | 100 (0)| 00:00:02

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 96 | 9312 | 100 (0)| 00:00:02

|* 2 | INDEX SKIP SCAN | IDX_OWNERID | 96 | | 97 (0)| 00:00:02

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<100)

filter("OBJECT_ID"<100)

結論：

INDEX SKIP SCAN中有個SKIP關鍵字，也就是說它是跳著掃描的。那麼想要跳躍掃描，必須是組合索引，如果是單列索引怎麼跳？

其次，組合索引的引導列不能出現在where條件中，如果引導列出現在where條件中，它幹嘛還跳躍掃描呢，直接INDEX RANGE SCAN不就得了。

其次要引導列基數很低，如果引導列基數很高，那麼它“跳”的次數就多了，效能就差了。

當執行計劃中出現了INDEX SKIP SCAN，可以直接在過濾列上面建立索引，使用INDEX RANGE SCAN代替INDEX SKIP SCAN。

從執行計劃中看到上面SQL走了索引跳躍掃描。最理想的情況應該是直接走where條件列object_id上的索引，並且走INDEX RANGE SCAN。

但是因為where條件列上面沒有直接建立索引，而是間接的被包含在組合索引中，為了避免全表掃描，CBO就選擇了索引跳躍掃描。

5、INDEX FULL SCAN表示索引全掃描，單塊讀，返回的資料是有序的(預設升序)。HINT: INDEX(表名/別名索引名)。

索引全掃描會掃描索引中所有的葉子塊(從左往右掃描)，如果索引很大，會產生嚴重效能問題(因為是單塊讀)，等待事件為db file sequential read。

它通常發生在下面三種情況：

①分頁語句，分頁語句在本書第八章中會詳細介紹，這裡不做贅述。

②SQL語句有order by選項，order by 的列都包含在索引中，並且order by 後列順序必須和索引列順序一致，order by的第一個列不能有過濾條件，如果有過濾條件就會走索引範圍掃描(INDEX RANGE SCAN)，並且表的資料量不能太大(資料量太大會走TABLE ACCESS FULL + SORT ORDER BY)。

有如下SQL：

select * from test order by object_id,owner;

建立如下索引(索引順序必須與排序順序一致，加0是為了讓索引能存NULL)：

SQL&get; create index idx_idowner on test(object_id,owner,0);

Index created.

執行如下SQL並且檢視執行計劃：

SQL&get; select * from test order by object_id,owner;

72462 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3870803568

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 73020 | 6916K| 1338 (1)| 00:00:17 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 73020 | 6916K| 1338 (1)| 00:00:17 |

| 2 | INDEX FULL SCAN | IDX_IDOWNER | 73020 | | 242 (1)| 00:00:03 |

-------------------------------------------------------------------------------------------

? 在進行SORT MERGE JOIN的時候，如果表資料量比較小，讓連線列走INDEX FULL SCAN可以避免排序。例如：

SQL&get; select /*+ use_merge(e,d) */

2 *

3 from emp e, dept d

4 where e.deptno = d.deptno;

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 844388907

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("E"."DEPTNO"="D"."DEPTNO")

filter("E"."DEPTNO"="D"."DEPTNO")

當看到執行計劃中有INDEX FULL SCAN，首先要檢查INDEX FULL SCAN是否有回表。

如果INDEX FULL SCAN沒有回表，要檢查索引段大小，如果索引段太大(上GB級別)，應該使用INDEX FAST FULL SCAN代替INDEX FULL SCAN，因為INDEX FAST FULL SCAN是多塊讀，INDEX FULL SCAN是單塊讀，即使使用了INDEX FAST FULL SCAN會產生額外的排序操作，也要用INDEX FAST FULL SCAN代替INDEX FULL SCAN。

如果INDEX FULL SCAN有回表，大多數情況下，這種執行計劃是錯誤的，因為INDEX FULL SCAN是單塊讀，回表也是單塊讀，這時應該走全表掃描，因為全表掃描是多塊讀。如果是分頁語句走了INDEX FULL SCAN然後回表，這時應該沒有太大問題，具體原因請各位讀者閱讀本書第八章分頁語句最佳化思路。

6、INDEX FAST FULL SCAN 表示索引快速全掃描，多塊讀。

HINT：INDEX_FFS(表名/別名索引名)。

當需要從表中查詢出大量資料，但是隻需要獲取表中部分列的資料，那麼這時可以利用索引快速全掃描代替全表掃描來提升效能。

索引快速全掃描的掃描方式與全表掃描的掃描方式是一樣，都是按區掃描，所以它可以多塊讀，並且可以並行掃描。

等待事件為db file scattered read，如果是並行掃描，等待事件為direct path read。

現有如下SQL：

select owner,object_name from test;

上面SQL沒有過濾條件，預設情況下會走全表掃描，但是因為Oracle是行儲存資料庫，全表掃描的時候會掃描表中所有的列，而上面查詢只訪問表中2個列，全表掃描會多掃描額外13個列，因此可以建立一個組合索引，使用索引快速全掃描代替全表掃描：

SQL&get; create index idx_ownername on test(owner,object_name,0);

Index created.

檢視SQL執行計劃：

SQL&get; select owner,object_name from test;

72462 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3888663772

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 73020 | 2210K| 79 (2)| 00:00:01 |

| 1 | INDEX FAST FULL SCAN| IDX_OWNERNAME | 73020 | 2210K| 79 (2)| 00:00:01 |

--------------------------------------------------------------------------------------

當需要從表中查詢出大量資料，但是隻需要獲取表中部分列的資料，那麼這時可以利用索引快速全掃描代替全表掃描來提升效能。

現有如下SQL：

select object_name from test where object_id<100;

上面SQL有過濾條件，根據過濾條件where object_id<100過濾資料之後只返回少量資料，一般情況下直接在object_id列建立索引，讓上面SQL走object_id列的索引即可：

SQL&get; create index idx_id on test(object_id);

Index created.

SQL&get; select object_name from test where object_id<100;

98 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3946039639

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 96 | 2880 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 96 | 2880 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_ID | 96 | | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<100)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

18 consistent gets

0 physical reads

0 redo size

2217 bytes sent via SQL*Net to client

485 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

因為上面SQL只查詢一個欄位，因此可以將select列放到組合索引中，避免回表：

SQL&get; create index idx_idname on test(object_id,object_name);

Index created.

再次檢視SQL的執行計劃：

SQL&get; select object_name from test where object_id<100;

98 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3678957952

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 96 | 2880 | 2 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_IDNAME | 96 | 2880 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID"<100)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

2217 bytes sent via SQL*Net to client

485 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

98 rows processed

現有如下SQL：

select object_name from test where object_id&get;100;

上面SQL過濾條件是where object_id&get;100，返回大量資料，應該走全表掃描，但是因為上面SQL只訪問一個欄位，因此可以走索引快速全掃描來代替全表掃描：

SQL&get; select object_name from test where object_id&get;100;

72363 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 252646278

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 72924 | 2136K| 73 (2)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| IDX_IDNAME | 72924 | 2136K| 73 (2)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"&get;100)

有讀者可能會有疑問，上面SQL能否走INDEX RANGE SCAN呢？INDEX RANGE SCAN是單塊讀，上面SQL會返回表中大量資料，“幾乎”會掃描索引中所有的葉子塊。INDEX FAST FULL SCAN是多塊讀，會掃描索引中所有的塊(根塊，所有的分支塊，所有的葉子塊)，雖然INDEX RANGE SCAN相比INDEX FAST FULL SCAN掃描的塊少(邏輯讀少)，但是INDEX RANGE SCAN是單塊讀，耗費的I/O次數比INDEX FAST FULL SCAN的I/O次數多，所以INDEX FAST FULL SCAN效能更好。

在做SQL最佳化的時候，不要全看邏輯讀來判斷一個SQL效能的好壞，物理I/O次數比邏輯讀更為重要。有時候邏輯讀高的執行計劃效能反而比邏輯讀低的執行計劃效能更好，因為邏輯讀高的執行計劃物理I/O次數比邏輯讀低的執行計劃物理I/O次數低。

在Oracle資料庫中，INDEX FAST FULL SCAN是用來代替TABLE ACCESS FULL的。因為Oracle是行儲存資料庫，TABLE ACCESS FULL會掃描表中所有的列，而INDEX FAST FULL SCAN只需要掃描表中部分列，INDEX FAST FULL SCAN就是因為Oracle是行儲存這個“缺陷”而產生的。

如果資料庫是Exadata，INDEX FAST FULL SCAN幾乎沒有用武之地，因為Exadata是行列混合儲存，在全表掃描的時候可以只掃描需要的列(Smart Scan)，這時就沒必要使用INDEX FAST FULL SCAN來代替全表掃描了。在Exadata中如果強行使用INDEX FAST FUL SCAN來代替全表掃描，這時反而會降低資料庫效能，因為沒辦法使用Exadata中的Smart Scan。

如果啟用了12c中的新特性IN MEMORY OPTION，INDEX FAST FULL SCAN幾乎也沒有用武之地了，因為表中的資料可以以列的形式存放在記憶體中，這時直接訪問記憶體中的資料即可。

7、INDEX FULL SCAN (MIN/MAX)

INDEX FULL SCAN (MIN/MAX)表示索引最小/最大值掃描，單塊讀，該訪問路徑發生在 SELECT MAX(COLUMN) FROM TABLE 或者SELECT MIN(COLUMN) FROM TABLE等SQL語句中。

INDEX FULL SCAN (MIN/MAX)只會訪問“索引高度”個索引塊，其效能與INDEX UNIQUE SCAN一樣，僅次於TABLE ACCESS BY USER ROWID。

現有如下SQL：

select max(object_id) from t;

該SQL查詢object_id的最大值，如果object_id列有索引，索引預設是升序排序的，這時只需要掃描索引中“最右邊”的葉子塊就能得到object_id的最大值。現在檢視上面SQL的執行計劃：

SQL&get; select max(object_id) from t;

Elapsed: 00:00:00.00

Execution Plan

----------------------------------------------------------

Plan hash value: 2448092560

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 186 (1)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_ID | 67907 | 862K| | |

---------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

430 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

現有另外一個SQL：

select max(object_id),min(object_id) from t;

該SQL要同時檢視object_id的最大值和最小值，如果想直接從object_id列的索引獲取資料，只需要掃描索引中“最左邊”和“最右邊”的葉子塊就可以。在Btree索引中，索引葉子塊是雙向指向的，如果要一次性獲取索引中“最左邊”和“最右邊”的葉子塊，就需要連帶的掃描“最大值”與“最小值”中間的葉子塊，而本案例中，中間葉子塊的資料並不是我們需要的。如果上面SQL走索引，會走INDEX FAST FULL SCAN，而不會走INDEX FULL SCAN，因為INDEX FAST FULL SCAN可以多塊讀，而INDEX FULL SCAN是單塊讀，兩者效能差距巨大(如果索引已經快取在buffer cache中，走INDEX FULL SCAN與INDEX FAST FULL SCAN效率幾乎一樣，因為不需要物理I/O)。需要注意的是，上面SQL沒有排除object_id為null，如果直接執行上面SQL，不會走索引：

SQL&get; select max(object_id),min(object_id) from t;

Elapsed: 00:00:00.02

Execution Plan

----------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 186 (1)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | TABLE ACCESS FULL| T | 67907 | 862K| 186 (1)| 00:00:03 |

---------------------------------------------------------------------------

排除object_id為null，檢視執行計劃：

SQL&get; select max(object_id),min(object_id) from t where object_id is not null;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3570898368

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 33 (4)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX FAST FULL SCAN| IDX_T_ID | 67907 | 862K| 33 (4)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

169 consistent gets

0 physical reads

0 redo size

501 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

從上面執行計劃中看到SQL走了INDEX FAST FULL SCAN，INDEX FAST FULL SCAN會掃描索引段中所有的塊，理想的情況是隻掃描索引中“最左邊”和“最右邊”的葉子塊。現在將上面SQL改寫為如下SQL：

select (select max(object_id) from t),(select min(object_id) from t) from dual;

檢視後的執行計劃：

SQL&get; select (select max(object_id) from t),(select min(object_id) from t) from dual;

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3622839313

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_ID | 67907 | 862K| | |

| 3 | SORT AGGREGATE | | 1 | 13 | | |

| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_ID | 67907 | 862K| | |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

527 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

原始SQL因為需要1次性從索引中取得最大值和最小值，所以導致走了INDEX FAST FULL SCAN。將SQL進行等價改寫之後，訪問了索引2次，一次取最大值，一次取最小值，從而避免掃描不需要的索引葉子塊，大大提升了查詢效能。

####以上講解出自道森培訓落落講師

【Oracle】索引的掃描方式

相關文章

【Oracle】 索引的掃描方式

相關文章

【Oracle】索引的掃描方式