Oracle 索引扫描的五种类型
?
之前在討論CBO和RBO的時候提到了索引掃描的幾種類型。
?
Oracle Optimizer CBO RBO
http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx
?
Oracle 索引 詳解
http://blog.csdn.net/tianlesoftware/archive/2010/03/05/5347098.aspx
?
Oracle Explain Plan
http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5827245.aspx
?
?
根據索引的類型與where限制條件的不同，有4種類型的Oracle索引掃描：????
（1）?????? 索引唯一掃描(index unique scan)
（2）?????? 索引范圍掃描(index range scan)
（3）?????? 索引全掃描(index full scan)
（4）?????? 索引快速掃描(index fast full scan)
（5） ??? 索引跳躍掃描（INDEX SKIP SCAN）
?
?
一. 索引唯一掃描(index unique scan)
通過唯一索引查找一個數值經常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，則至少要有組合索引的引導列參與到該查詢中，如創建一個索引：create index idx_test on emp(ename, deptno, loc)。則select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’語句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而select ename from emp where deptno = ‘DEV’語句則不會使用該索引，因為where子句種沒有引導列。如果存在UNIQUE 或PRIMARY KEY 約束(它保證了語句只存取單行)的話，Oracle經常實現唯一性掃描。
?
如：
SQL> set autot traceonly exp;?? -- 只顯示執行計劃
SQL> select * from scott.emp t where t.empno=10;
執行計劃
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id? | Operation?????????????????? | Name?? | Rows? | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? ???????|??????? |???? 1 |??? 38 |???? 1?? (0)| 00:0
|?? 1 |? TABLE ACCESS BY INDEX ROWID| EMP??? |???? 1 |??? 38 |???? 1?? (0)| 00:0
|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_EMP |???? 1 |?????? |???? 0?? (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - access("T"."EMPNO"=10)
?
?
二．索引范圍掃描(index range scan)
使用一個索引存取多行數據，同上面一樣，如果索引是組合索引，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’語句返回多行數據，雖然該語句還是使用該組合索引進行查詢，可此時的存取方法稱為索引范圍掃描。
在唯一索引上使用索引范圍掃描的典型情況下是在謂詞(where限制條件)中使用了范圍操作符(如>、<、<>、>=、<=、between)
?
使用索引范圍掃描的例子：
?
SQL> select empno,ename from scott.emp? where empno > 7876 order by empno;
執行計劃
----------------------------------------------------------
Plan hash value: 169057108
--------------------------------------------------------------------------------
| Id? | Operation?????????????????? | Name?? | Rows? | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????? |??????? |???? 1 |??? 10 |???? 2?? (0)| 00:0
|?? 1 |? TABLE ACCESS BY INDEX ROWID| EMP??? |???? 1 |??? 10 |???? 2?? (0)| 00:0
|*? 2 |?? INDEX RANGE SCAN????????? | PK_EMP |???? 1 |?????? |???? 1?? (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - access("EMPNO">7876)
?
在非唯一索引上，謂詞可能返回多行數據，所以在非唯一索引上都使用索引范圍掃描。
?
使用index rang scan的3種情況：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。
(b) 在組合索引上，只使用部分列進行查詢，導致查詢出多行。
(c) 對非唯一索引列上進行的任何查詢。
?
?
三．索引全掃描(index full scan)
與全表掃描對應，也有相應的全Oracle索引掃描。在某些情況下，可能進行全Oracle索引掃描而不是范圍掃描，需要注意的是全Oracle索引掃描只在CBO模式下才有效。 CBO根據統計數值得知進行全Oracle索引掃描比進行全表掃描更有效時，才進行全Oracle索引掃描，而且此時查詢出的數據都必須從索引中可以直接得到。
?
全Oracle索引掃描的例子：
?
SQL> create index big_emp on scott.emp(empno,ename);
索引已創建。
SQL> select empno, ename from scott.emp order by empno,ename;
執行計劃
----------------------------------------------------------
Plan hash value: 322359667
----------------------------------------------------------------------------
| Id? | Operation??????? | Name??? | Rows? | Bytes | Cost (%CPU)| Time???? |
----------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT |???????? |??? 14 |?? 140 |???? 1?? (0)| 00:00:01 |
|?? 1 |? INDEX FULL SCAN | BIG_EMP |??? 14 |?? 140 |???? 1?? (0)| 00:00:01 |
----------------------------------------------------------------------------
?
?
四． 索引快速掃描(index fast full scan)
掃描索引中的所有的數據塊，與 index full scan很類似，但是一個顯著的區別就是它不對查詢出的數據進行排序，即數據不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執行時間。
?
索引快速掃描的例子：
SQL> select /*+ index_ffs(dave index_dave) */ id from dave where id>0;
執行計劃
----------------------------------------------------------
Plan hash value: 674200218
--------------------------------------------------------------------------------
| Id? | Operation??????????? | Name?????? | Rows? | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???? |??????????? |???? 8 |??? 24 |???? 2?? (0)| 00:00:0
|*? 1 |? INDEX FAST FULL SCAN| INDEX_DAVE |???? 8 |??? 24 |???? 2?? (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("ID">0)
?
為了實現這個效果，折騰了半天，最終還是用hint來了.
?
Oracle Hint
http://blog.csdn.net/tianlesoftware/archive/2010/03/05/5347098.aspx
?
?
?
五. 索引跳躍掃描（INDEX SKIP SCAN）
??????????? INDEX SKIP SCAN，發生在多個列建立的復合索引上，如果SQL中謂詞條件只包含索引中的部分列，并且這些列不是建立索引時的第一列時，就可能發生INDEX SKIP SCAN。這里SKIP的意思是因為查詢條件沒有第一列或前面幾列，被忽略了。
?
Oracle 10g的文檔如下：
??????????? Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
??????????? Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
??????????? --skip scan 讓組合索引（composite index）邏輯的split 成幾個子索引。如果在在查詢時，第一個列沒有指定，就跳過它。
???????????
??????????? The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
??????????? -- 建議將distinct 值小的列作為組合索引的引導列，即第一列。
?
Example 13-5 Index Skip Scan
??????????? Consider, for example, a table employees (sex, employee_id, address) with a composite index on (sex, employee_id). Splitting this composite index would result in two logical subindexes, one for M and one for F.
For this example, suppose you have the following index data:
('F',98)
('F',100)
('F',102)
('F',104)
('M',101)
('M',103)
('M',105)
?
The index is split logically into the following two subindexes:
??????????? （1）The first subindex has the keys with the value F.
??????????? （2）The second subindex has the keys with the value M.
?
Figure 13-2 Index Skip Scan Illustration
The column sex is skipped in the following query:
SELECT *
?? FROM employees
WHERE employee_id = 101;
?
??????????? A complete scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the value M.
?
測試：
創建表：
SQL> create table dave_test as select owner,object_id,object_type,created from dba_objects;
Table created.
?
創建組合索引
SQL> create index idx_dave_test_com on dave_test(owner,object_id,object_type);
Index created.
?
--收集表的統計信息
SQL> exec dbms_stats.gather_table_stats('SYS','DAVE_TEST');
PL/SQL procedure successfully completed.
?
SQL> set autot traceonly exp;
?
指定組合索引的所有字段時，使用Index range scan：
SQL> select * from dave_test where owner='SYS' and object_id=20 and object_type='TABLE';
?
Execution Plan
----------------------------------------------------------
Plan hash value: 418973243
?
--------------------------------------------------------------------------------
| Id? | Operation?????????????????? | Name????????????? | Rows? | Bytes | Cost (
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????? |?????????????????? |???? 1 |??? 27 |???? 2
|?? 1 |? TABLE ACCESS BY INDEX ROWID| DAVE_TEST???????? |???? 1 |??? 27 |???? 2
|*? 2 |?? INDEX RANGE SCAN ?????????| IDX_DAVE_TEST_COM |???? 1 |?????? |???? 1
--------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 2 - access("OWNER"='SYS' AND "OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
?
指定組合索引的2個字段時，使用的還是index range scan：
SQL> select * from dave_test where owner='SYS' and object_id=20;
?
Execution Plan
----------------------------------------------------------
Plan hash value: 418973243
?
--------------------------------------------------------------------------------
| Id? | Operation?????????????????? | Name????????????? | Rows? | Bytes | Cost (
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????? |?????????????????? |???? 1 |??? 27 |???? 3
|?? 1 |? TABLE ACCESS BY INDEX ROWID| DAVE_TEST???????? |???? 1 |??? 27 |???? 3
|*? 2 |?? INDEX RANGE SCAN? ????????| IDX_DAVE_TEST_COM |???? 1 |?????? |???? 2
--------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 2 - access("OWNER"='SYS' AND "OBJECT_ID"=20)
?
指定組合索引的引導列，即第一個列時，不走索引，走全表掃描
SQL> select * from dave_test where owner='SYS';
?
Execution Plan
----------------------------------------------------------
Plan hash value: 1539627441
?
-------------------------------------------------------------------------------
| Id? | Operation???????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |?????????? | 23567 |?? 621K|??? 52?? (4)| 00:00:01 |
|*? 1 |? TABLE ACCESS FULL| DAVE_TEST | 23567 |?? 621K|??? 52?? (4)| 00:00:01 |
-------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 1 - filter("OWNER"='SYS')
?
指定組合索引的非引導列，使用Index skip scan：
SQL> select * from dave_test where object_id=20 and object_type='TABLE';
?
Execution Plan
----------------------------------------------------------
Plan hash value: 3446962311
?
--------------------------------------------------------------------------------
| Id? | Operation?????????????????? | Name????????????? | Rows? | Bytes | Cost (
--------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??????????? |?????????????????? |???? 1 |??? 27 |??? 22
|?? 1 |? TABLE ACCESS BY INDEX ROWID| DAVE_TEST????? ???|???? 1 |??? 27 |??? 22
|*? 2 |? ?INDEX SKIP SCAN ??????????| IDX_DAVE_TEST_COM |???? 1 |?????? |??? 21
--------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
?????? filter("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')
?
指定組合索引的最后一列，不走索引，走全表掃描
SQL> select * from dave_test where object_type='TABLE';
?
Execution Plan
----------------------------------------------------------
Plan hash value: 1539627441
?
-------------------------------------------------------------------------------
| Id? | Operation???????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |?????????? |? 1774 | 47898 |??? 52?? (4)| 00:00:01 |
|*? 1 |? TABLE ACCESS FULL| DAVE_TEST |? 1774 | 47898 |??? 52?? (4)| 00:00:01 |
-------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 1 - filter("OBJECT_TYPE"='TABLE')
?
指定組合索引的頭尾2列，不走索引：
SQL> select * from dave_test where owner='SYS' and object_type='TABLE';
?
Execution Plan
----------------------------------------------------------
Plan hash value: 1539627441
?
-------------------------------------------------------------------------------
| Id? | Operation???????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |?????????? |?? 830 | 22410 |??? 52?? (4)| 00:00:01 |
|*? 1 |? TABLE ACCESS FULL| DAVE_TEST |?? 830 | 22410 |??? 52?? (4)| 00:00:01 |
-------------------------------------------------------------------------------
?
Predicate Information (identified by operation id):
---------------------------------------------------
?
?? 1 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')
?
??????????? 通過以上測試，和之前官網的說明，Index skip scan 僅是在組合索引的引導列，即第一列沒有指定，并且非引導列指定的情況下。
?
??????????? 聯合索引選擇性更高咯，所占空間應當是比單獨索引要少，因為葉節點節省了重復的rowid，當然branch節點可能稍微多一點。
禁用skip scan:
alter system set “_optimizer_skip_scan_enabled” = false scope=spfile;
?
?
?
?
?
?
整理自網絡
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
網上資源： http://tianlesoftware.download.csdn.net
相關視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(滿); DBA2 群：62697977(滿)
DBA3 群：63306533;???? 聊天 群：40132017
轉載于:https://www.cnblogs.com/hibernate315/archive/2010/08/31/2399198.html
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀總結
以上是生活随笔為你收集整理的Oracle 索引扫描的五种类型的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 研磨设计模式之 单例模式-3
- 下一篇： 在SharePoint 2010中通过S