版權聲明：本文發布於http://www.cnblogs.com/yumiko/，版權由Yumiko_sunny所有，歡迎轉載。轉載時，請在文章明顯位置注明原文鏈接。若在未經作者同意的情況下，將本文內容用於商業用途，將保留追究其法律責任的權利。如果有問題，請以郵箱方式聯系作者（[email protected]）。
理解oracle索引掃描類型的特點以及具體觸發的條件，對於通過合理地使用索引，進行sql優化至關重要（例如組合索引的引導列的選擇問題）。
在總結索引掃描類型前，需要再次強調關於索引特點的幾個關鍵點：
其他的一些特點，可參閱前面的幾篇總結。
此外，為避免概念的混淆，再次說明一下：“索引類型”主要探討的是索引的幾種類別的問題，而“索引掃描類型”主要探討的是索引掃描的幾種具體實現方法的問題。
Oracle提供了五種索引掃描類型，根據具體索引類型、數據分布、約束條件以及where限制的不同進行選擇：
索引唯一掃描，僅僅針對唯一索引的掃描，且僅適用於等值（=）條件的查詢。從結果集看，至多返回一條記錄。
具體情況分析：
需要注意：
示例：
--創建唯一索引
Yumiko@Sunny >create unique index ind_test_normal on test_normal(empno,ENAME,SAL); Index created.
Yumiko@Sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes where TABLE_NAME='TEST_NORMAL'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------- --------------------------- --------------- --------- IND_TEST_NORMAL NORMAL TEST_NORMAL UNIQUE
--查詢記錄並查看執行計劃 Yumiko@Sunny >select * from TEST_NORMAL where empno=7369 and ENAME='SMITH' and sal=800; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7369 SMITH CLERK 800 Execution Plan ---------------------------------------------------------- Plan hash value: 1399315988 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes|Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1| 39| 1 (0)| 00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID| TEST_NORMAL | 1| 39| 1 (0)| 00:00:01| |* 2 | INDEX UNIQUE SCAN | IND_TEST_NORMAL| 1| | 0 (0)| 00:00:01| ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7369 AND "ENAME"='SMITH' AND "SAL"=800) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 16 physical reads 0 redo size 581 bytes sent via SQL*Net to client 458 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
索引范圍掃描，不僅可以針對唯一索引，也可以針對非唯一索引。從結果集看，可以是一條記錄，也可以是多條記錄。
具體情況分析：
需要注意：
示例：
--創建普通索引
Yumiko@Sunny >create index ind_test_normal on test_normal(empno,ENAME,SAL); Index created.
--驗證普通索引創建情況，確認為非唯一索引 Yumiko@Sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes where TABLE_NAME='TEST_NORMAL'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------- --------------------------- --------------- --------- IND_TEST_NORMAL NORMAL TEST_NORMAL NONUNIQUE
--查詢記錄並觀察執行計劃，此時掃描類型為index range scan Yumiko@Sunny >select * from TEST_NORMAL where empno=7369 and ENAME='SMITH' and sal=800; EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7369 SMITH CLERK 800 Execution Plan ----------------------------------------------- Plan hash value: 67814702 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID| TEST_NORMAL | 1 | 18 | 2 (0)| 00:00:01| |* 2 | INDEX RANGE SCAN | IND_TEST_NORMAL| 1 | | 1 (0)| 00:00:01| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7369 AND "ENAME"='SMITH' AND "SAL"=800) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 16 physical reads 0 redo size 717 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
該索引掃描方式主要發生在組合索引上，且組合索引的引導列未被指定在檢索條件中的情況下發生。
在組合索引中，無論該索引是否為唯一索引。當引導列未被指定在檢索條件的情況下，可能會發生“索引跳躍掃描”
以下黃色字體內容摘引自http://book.51cto.com/art/201312/422441.htm
對於組合索引，在無前導列的情況下還能使用索引，是因為Oracle幫我們對該索引的前導列的所有distinct值做了遍歷。
所謂的對目標索引的所有distinct值做遍歷，其實際含義相當於對原目標SQL做等價改寫（即把要用的目標索引的所有前導列的distinct值都加進來）。
例如：create index idx_employee on employee(gender,employee_id)，表數據量10000行，其中gender列只有“M”跟“F”值。
當執行select * from employee where employee_id = 100時，相當於執行了等價改寫，改寫為：
select * from employee where gender = 'F' and employee_id = 100
union all
select * from employee where gender = 'M' and employee_id = 100;
因此，Oracle中的索引跳躍式掃描僅僅適用於那些目標索引前導列的distinct值數量較少、後續非前導列的可選擇性又非常好的情形，因為索引跳躍式掃描的執行效率一定會隨著目標索引前導列的distinct值數量的遞增而遞減。否則將執行全表掃描。
示例：
--查詢測試表的總數據量
--該表總數據為22928行
Yumiko@Sunny >select count(*) from test; COUNT(*) ---------- 22928
--查詢測試表中owner列的列值數據分布情況
--從查詢結果看，對於owner列，在22928行的數據中，只分布了3個不同的列值，數據數值的分布情況較為集中 Yumiko@Sunny >select count(distinct owner) from test; COUNT(DISTINCTOWNER) -------------------- 3
--查詢測試表中object_id列的數值分布情況
--從查詢結果看，對於object_id列，在22928行的數據中，分布了22912個不同的數值，該列整體的數值分布情況較為零散 Yumiko@Sunny >select count(distinct object_id) from test; COUNT(DISTINCTOBJECT_ID) ------------------------ 22912 Yumiko@Sunny >desc test Name Null? Type ---------------------------------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)
--以數值較為集中的owner作為組合索引的引導列，創建普通索引 Yumiko@Sunny >create index test on test(owner,OBJECT_ID); Index created. Yumiko@Sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes where TABLE_NAME='TEST'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------- ------------------- --------------- --------- TEST NORMAL TEST NONUNIQUE --收集測試表最新的統計信息 Yumiko@Sunny >analyze table test compute statistics for table for all columns for all indexes; Table analyzed.
--清空buffer cache緩沖池，保證無測試表的數據塊存在在內存中，防止影響到測試結果 Yumiko@Sunny >alter system flush buffer_cache; System altered.
--使用之前創建的組合索引，以組合索引非引導作為條件查詢列進行條件查詢
--從執行計劃看，oracle用到了索引掃描，而且采用index skip scan的方式進行掃描 Yumiko@Sunny >select * from test where object_id=3; Execution Plan ---------------------------------------------------------- Plan hash value: 2389257771 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86| 5 (0)| 00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 86| 5 (0)| 00:00:01| |* 2 | INDEX SKIP SCAN | TEST | 1 | | 4 (0)| 00:00:01| ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=3) filter("OBJECT_ID"=3) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 24 physical reads 0 redo size 1402 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--刪除之前的索引 Yumiko@Sunny >drop index test; Index dropped.
--以數值分布較為零散的object_id列作為引導列，再次創建普通索引 Yumiko@Sunny >create index test on test(OBJECT_ID,owner); Index created.
--收集測試表最新的統計信息 Yumiko@Sunny >analyze table test compute statistics for table for all columns for all indexes; Table analyzed.
--清空buffer cache緩沖池，避免影響數據測試 Yumiko@Sunny >alter system flush buffer_cache; System altered.
--使用上面剛剛創建的組合索引的非引導列owner作為條件查詢列進行查詢操作
--從執行計劃看，此次oracle未選擇走索引掃描，而是采用了全表掃描的方式
--到此，印證了之前對於index skip scan方式選擇的說法 Yumiko@Sunny >select * from test where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8 | 688| 74 (2)| 00:00:01| |* 1 | TABLE ACCESS FULL| TEST | 8 | 688| 74 (2)| 00:00:01| ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='BI') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 318 consistent gets 315 physical reads 0 redo size 1583 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
對於索引全掃描，就是使用目標索引進行索引掃描時，會掃描所有索引葉塊的所有索引行。
對於索引全掃描，只適用於CBO。
對於索引全掃描，使用單塊讀取的方式，有序讀取索引塊。
對於索引全掃描，從結果集看，結果全部源於索引塊，而且由於已經按照索引鍵值順序排序，因此不需要單獨排序
對於索引全掃描，會話會產生db file sequential reads事件。
具體情況分析：
示例：
--為測試表TEST_NORMAL的empno列添加非空約束
Yumiko@Sunny >alter table TEST_NORMAL modify(empno not null); Table altered.
--以empno列以及ename列作為組合，創建普通索引 Yumiko@Sunny >create index TEST_NORMAL_ind on TEST_NORMAL(empno,ename); Index created.
--查詢上面組合索引涉及的ename列，該列不存在非空約束
--從執行計劃看，oracle選擇了index full scan的索引掃描方式，且結果集僅僅來源於索引塊（沒有根據rowid返回數據集的記錄，說明無訪問數據塊） Yumiko@Sunny >select ename from TEST_NORMAL; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2425626010 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 70 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | TEST_NORMAL_IND | 14 | 70 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 8 physical reads 0 redo size 705 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
對於索引快速掃描，就是使用目標索引進行索引掃描時，會掃描所有索引葉塊的所有索引行。
對於索引快速掃描，只適用於CBO。
對於索引快速掃描，使用多塊讀取的方式，讀取索引塊。（這種方式相較於索引全掃描，獲取數據的效率更高）
對於索引快速掃描，從結果集看，結果全部源於索引塊，但數據結果不一定有序。
對於索引快速掃描，會話會產生db file scattered reads事件。
具體情況分析：
參閱上面的索引全掃描，兩者所謂的同宗，只是不同的需求（對於結果的響應速度或是數據序列，當然CBO優化器也會進行內部的計算評估選取最優執行路徑），產生的不同的執行結果，下面的示例會展示
示例：
--查詢測試表的數據量，顯示此表有22928行數據
Yumiko@Sunny >select count(*) from test; COUNT(*) ---------- 22928
--確認數據列中不含非空約束 Yumiko@Sunny >desc test Name Null? Type -------------------------------------------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)
--針對object_id列，創建普通索引 Yumiko@Sunny >create index test on test(object_id); Index created.
--確認上面創建的索引為非唯一索引 Yumiko@Sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS from user_indexes where TABLE_NAME='TEST'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------------------------------------------------------- TEST NORMAL TEST NONUNIQUE
--清空buffer_cache緩沖池數據，防止造成測試的影響 Yumiko@Sunny >alter system flush buffer_cache; System altered.
--清空shared pool緩沖池數據，防止對測試造成影響 Yumiko@Sunny >alter system flush shared_pool; System altered.
--收集測試表最新的統計信息 Yumiko@Sunny >analyze table test compute statistics for table for all columns for all indexes; Table analyzed.
--打開會話追蹤，僅查看執行計劃 Yumiko@Sunny >set autotrace trace --查詢索引列object，同時使用is not null作為查詢條件。
--由於b-tree索引中不記錄null值信息，而且該列不存在非空約束，通過not null條件指定，讓oracle不必考慮null值。
--從執行計劃看，由於讀取的數據量很大且不用排序，oracle選擇了更快的多塊讀方式的index fast full scan，盡快獲取數據。
--同樣的，此時未出現access by index rowid的情況，說明未查詢數據塊，僅僅查詢了索引塊。 Yumiko@Sunny >select object_id from test where object_id is not null; 22928 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1645531115 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23290 | 295K| 14 (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| TEST | 23290 | 295K| 14 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1582 consistent gets 53 physical reads 0 redo size 502642 bytes sent via SQL*Net to client 17277 bytes received via SQL*Net from client 1530 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22928 rows processed --修改上面的查詢，增加排序操作。
--從執行計劃看，由於此次增加了排序操作，oracle選擇了偏向有序讀取的index full scan的方式進行掃描。
--同樣的，此次未查詢數據塊（不存在access by index rowid）。 Yumiko@Sunny >select object_id from test where object_id is not null order by object_id; 22928 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3883652822 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23290 | 295K| 60 (2)| 00:00:01 | |* 1 | INDEX FULL SCAN | TEST | 23290 | 295K| 60 (2)| 00:00:01 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1578 consistent gets 64 physical reads 0 redo size 502642 bytes sent via SQL*Net to client 17277 bytes received via SQL*Net from client 1530 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22928 rows processed --再次修改查詢，取消not null條件查詢以及排序操作。
--此時，對於沒有非空約束的object_id列，增加了null值的可能性，而b-tree索引不保存null信息。
--此次，oracle選擇了全表掃描的方式。 Yumiko@Sunny >select object_id from test; 22928 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22928 | 91712 | 74 (2)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 22928 | 91712 | 74 (2)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1830 consistent gets 315 physical reads 0 redo size 415626 bytes sent via SQL*Net to client 17277 bytes received via SQL*Net from client 1530 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22928 rows processed
由於篇幅有限，關於索引掃描類型的總結到此為止。若存在內容上的遺漏，或者錯誤，歡迎各位指教。
