0.參考文獻：
Index Full Scan && Index Range Scan
oracle-index unique scan 與index range scan等的區別
index range scan,index fast full scan,index skip scan發生的條件
1.oracle訪問表的方式
oracle 訪問表中記錄有三種方式：
ORACLE 採用三種訪問表中記錄的方式: 全表掃描、通過ROWID訪問表、索引掃描
2.全表掃描（Full Table Scans, FTS）
為實現全表掃描，Oracle順序地訪問表中每條記錄，並檢查每一條記錄是否滿足WHERE語句的限制條件。ORACLE採用一次讀入多個資料塊(database block)的方式優化全表掃描，而不是隻讀取一個資料塊，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描。需要注意的是隻有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個資料塊只被讀一次。
使用FTS的前提條件：在較大的表上不建議使用全表掃描，除非取出資料的比較多，超過總量的5% -- 10%，或你想使用並行查詢功能時。
全表掃描例項（TABLE ACCESS FULL）
建立表並插入資料，並進行查詢。
View Code
--建立資料庫 CREATE TABLE "USERINFO" ( "NO" INT , "NAME" VARCHAR2(50), "BIRTHDAY" DATE ) --建立序列 CREATE SEQUENCE SEQ_USERINFO_NO INCREMENT BY 1 -- 每次加幾個 START WITH 1 -- 從1開始計數 --插入100000條資料 begin for i in 1..100000 loop INSERT INTO USERINFO VALUES(SEQ_USERINFO_NO.nextval,'XUWEI',SYSDATE); end loop; end; / --查詢NO=5000的結果 set autotrace traceonly select * from userinfo where no = 5000; 執行計劃 ---------------------------------------------------------- Plan hash value: 3576123897 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 49 | 102 (0)| 00:00:02 | |* 1 | TABLE ACCESS FULL| USERINFO | 1 | 49 | 102 (0)| 00:00:02 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NO"=5000) Note ----- - dynamic sampling used for this statement 統計資訊 ---------------------------------------------------------- 5 recursive calls 0 db block gets 119023 consistent gets 0 physical reads 25048 redo size 391 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
從查詢計劃我們可以看到所採用的查詢方式是“TABLE ACCESS FULL”，這就是全表掃描。也正是因為採用全表掃描，所以consistent gets會很大。
3.通過ROWID訪問表（table access by ROWID）
ROWID指出了該行所在的資料檔案、資料塊以及行在該塊中的位置，所以通過ROWID來存取資料可以快速定位到目標資料上，是Oracle存取單行資料的最快方法。為了通過ROWID存取表，Oracle 首先要獲取被選擇行的ROWID，或者從語句的WHERE子句中得到，或者通過表的一個或多個索引的索引掃描得到。Oracle然後以得到的ROWID為依據定位每個被選擇的行。下面給出使用rowid訪問表的例項。
3.1.單個rowid的情形
View Code
--檢視錶上rowid select rowid,empno,ename from emp where deptno=20; --得到的查詢結果 ROWID EMPNO ENAME ------------------ ---------- ---------- AAAX7bAAEAAAo1VAAA 7369 SMITH AAAX7bAAEAAAo1VAAD 7566 JONES AAAX7bAAEAAAo1VAAH 7788 SCOTT AAAX7bAAEAAAo1VAAK 7876 ADAMS AAAX7bAAEAAAo1VAAM 7902 FORD --根據rowid查詢記錄 set autotrace on select empno,ename from emp where rowid='AAAX7bAAEAAAo1VAAM'; --查詢結果 EMPNO ENAME ---------- ---------- 7902 FORD 執行計劃 ---------------------------------------------------------- Plan hash value: 1116584662 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- 統計資訊 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 477 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
查詢計劃中說明該查詢是的表訪問方式是”TABLE ACCESS BY USER ROWID“，也就是直接通過USER ROWID來訪問，這也是為什麼只需要1次consistent gets的原因。
3.2.多個rowid的傾向
使用如下查詢進行範圍查詢
View Code
--使用多個rowid的情形 select empno,ename from emp where rowid in ('AAAX7bAAEAAAo1VAAA','AAAX7bAAEAAAo1VAAD'); --查詢結果 EMPNO ENAME ---------- ---------- 7369 SMITH 7566 JONES 執行計劃 ---------------------------------------------------------- Plan hash value: 1106538681 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY USER ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement 統計資訊 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
查詢計劃分析：
- 上面的執行計劃中出現了INLIST ITERATOR，即INLIST迭代，該操作說明其子操作多次重複時，會出現該操作。
- 由於我們使用了in運算，且傳遞了2個rowid，故出現INLIST迭代操作
- 迭代操作意味著條件中的物件列表一個接一個的迭代傳遞給子操作
- 此時統計資訊中的consistent gets為2，並不是因為傳入的rowid有2個，假如傳入的rowid有4個，consistent gets也等於4。
注意：使用ROWID進行查詢的前提是我們明確知道了一個正確的ROWID，然後通過這個ROWID進行查詢。所以這裡所提到的所有ROWID必須是真實存在的，否則會報錯。
4.索引掃描（Index scan）
我們先通過index查詢到資料對應的rowid值(對於非唯一索引可能返回多個rowid值)，然後根據rowid直接從表中得到具體的資料，這種查詢方式稱為索引掃描或索引查詢(index lookup)。一個rowid唯一的表示一行資料，該行對應的資料塊是通過一次i/o得到的，在此情況下該次i/o只會讀取一個資料庫塊。
在索引中，除了儲存每個索引的值外，索引還儲存具有此值的行對應的ROWID值。索引掃描可以由2步組成：(1) 掃描索引得到對應的rowid值。 (2) 通過找到的rowid從表中讀出具體的資料。
根據索引的型別與where限制條件的不同，有4種型別的索引掃描。
4.1.索引範圍掃描(INDEX RANGE SCAN)
使用一個索引存取多行資料，在唯一索引上使用索引範圍掃描的典型情況下是在謂詞(where限制條件)中使用了範圍操作符(如>、<、<>、>=、<=、between)。在非唯一索引上，謂詞"="也可能返回多行資料，所以在非唯一索引上都使用索引範圍掃描。
使用index rang scan的3種情況：
- 在唯一索引列上使用了range操作符(> < <> >= <= between)
- 在組合索引上，只使用部分列進行查詢，導致查詢出多行
- 對非唯一索引列上進行的任何查詢。
4.1.1例項
跟全表掃描一樣，使用userinfo表作為例項介紹
View Code
--為userinfo表中的no欄位建立索引 IX_USERINFO_NO create index IX_USERINFO_NO on USERINFO(NO); --查詢no在1000到1005之間的記錄 select * from userinfo where no between 1000 and 1005; --查詢結果 NO NAME BIRTHDAY ---------- -------------------------------------------------- -------------- 1000 XUWEI 12-6月 -12 1001 XUWEI 12-6月 -12 1002 XUWEI 12-6月 -12 1003 XUWEI 12-6月 -12 1004 XUWEI 12-6月 -12 1005 XUWEI 12-6月 -12 執行計劃 ---------------------------------------------------------- Plan hash value: 1066629497 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 126 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 7 | 126 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 7 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO">=1000 AND "NO"<=1005) 統計資訊 ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 692 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed
4.2.索引唯一掃描(INDEX UNIQUE SCAN)
通過唯一索引查詢一個數值經常返回單個ROWID。如果存在UNIQUE 或PRIMARY KEY 約束(它保證了語句只存取單行)的話，Oracle經常實現唯一性掃描。
下面依然以userinfo表為例進行舉例說明。首先刪除原先在no欄位上建立的索引，然後將no欄位設為主鍵，再進行“=”的查詢，比如查詢no=5000的欄位，程式碼例項如下：
View Code
--刪除索引 drop index IX_USERINFO_NO; --新增主鍵 alter table USERINFO add constraint PK_USERINFO_NO primary key(NO); --查詢no為5000的記錄 set autotrace traceonly select * from userinfo where no = 5000; 執行計劃 ---------------------------------------------------------- Plan hash value: 4161181038 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) 統計資訊 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 447 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
4.3.索引全掃描(index full scan)
有順序的輸出，不能並行訪問索引。
4.4.索引快速掃描(index fast full scan)
Fast Full Index Scans（來自官方文件）
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query（組合索引中的列包含了需要查詢的所有列）, and at least one column in the index key has the NOT NULL constraint（至少有一個有非空約束）. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O（一次可以讀多個塊，跟全表掃描一樣） and can be parallelized just like a table scan.
例項
View Code
--建立測試表 create table test as select * from all_objects; --建立唯一索引 create unique index IX_OBJECT_ID on test(object_id); --建立組合索引 create index IX2 on test(owner,object_name,object_type); --INDEX RANGE SCAN select owner,object_name,object_type from test where owner='SCOTT'; 執行計劃 ---------------------------------------------------------- Plan hash value: 3671601508 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 270 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IX2 | 6 | 270 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------- --INDEX FAST FULL SCAN select owner, object_name,object_type from test where object_name='EMP' ; 執行計劃 ---------------------------------------------------------- Plan hash value: 4039984554 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 495 | 136 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IX2 | 11 | 495 | 136 (1)| 00:00:02 | ----------------------------------------------------------------------------- --INDEX FAST FULL SCAN select owner, object_name,object_type from test where object_type='INDEX'; 執行計劃 ---------------------------------------------------------- Plan hash value: 4039984554 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1288 | 57960 | 136 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IX2 | 1288 | 57960 | 136 (1)| 00:00:02 | -----------------------------------------------------------------------------
4.5.index skip scan(索引跳躍式掃描)
Index Skip Scans
Index skip scans improve index scans by no nprefix columns. Often, scanning index blocks is faster than sc anning 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.
The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageo us 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.
當查詢可以通過組合索引得到結果，而且返回結果很少，並且where條件中沒有包含索引引導列的時候，可能執行index skip scan。
索引跳躍式掃描發生的條件:
- 必須是組合索引。
- 引導列沒有出現在where條件中。