Oracle的執行計劃及資料存取方式 (ROWID,掃描方式等)

一．相關的概念

ROWID --   rowid是一個偽列，既然是偽列，那麼這個列就不是使用者定義，
而是系統己給加上的。對每個表都有一個rowid的偽列，但是表中並不物理
儲存ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除
該列，也不能對該列的值進行修改、插入。一旦一行資料插入資料庫，
則rowid在該行的生命週期內是唯一的，即即使該行產生行遷移，行的rowid
也不會改變。

ROWID的格式 -- OOOOOOFFFBBBBBBRRR , OOOOOO表示該行所在的物件號，FFF
表示該行所在的相對檔案號，BBBBBB表示該資料行所在的資料塊號，RRR表示
該行在資料塊中的行號 。ROWID採用64進位制來表示，可以是A~Z,a~z,0~9,/,+
這64個字元來表示。 很多針對rowid的操作都可以藉助dbms_rowid包來進行。

Recursive SQL概念：有時為了執行使用者發出的一個sql語句，Oracle必須執行
一些額外的語句，我們將這些額外的語句稱之為'recursive calls'或'recursive
SQL statements'。如當一個DDL語句發出後，ORACLE總是隱含的發出一些recursive
SQL語句，來修改資料字典資訊，以便使用者可以成功的執行該DDL語句。當需要的數
據字典資訊沒有在共享記憶體中時，經常會發生Recursive calls，這些Recursive calls
會將資料字典資訊從硬碟讀入記憶體中。使用者不必關心這些recursive SQL語句的執行
情況，在需要的時候，ORACLE會自動的在內部執行這些語句。當然DML語句與SELECT都
可能引起recursive SQL。簡單的說，我們可以將觸發器視為recursive SQL。

Row Source(行源)：用在查詢中，由上一操作返回的符合條件的行的集合，即可以是
表的全部行資料的集合；也可以是表的部分行資料的集合；也可以為對上2個row source
進行連線操作(如join連線)後得到的行資料集合。

Predicate(謂詞)：一個查詢中的WHERE限制條件

Driving Table(驅動表)：該表又稱為外層表(OUTER TABLE)。這個概念用於巢狀於HASH
連線中。如果該row source返回較多的行資料，則對所有的後續操作有負面影響。注意
此處雖然翻譯為驅動表，但實際上翻譯為驅動行源(driving row source)更為確切。一
般說來，是應用查詢的限制條件後，返回較少行源的表作為驅動表，所以如果一個大表
在WHERE條件有有限制條件(如等值限制)，則該大表作為驅動表也是合適的，所以並不
是隻有較小的表可以作為驅動表，正確說法應該為應用查詢的限制條件後，返回較少行
源的表作為驅動表。在執行計劃中，應該為靠上的那個row source，後面會給出具體
說明。在我們後面的描述中，一般將該表稱為連線操作的row source 1 。 

Probed Table(被探查表)：該表又稱為內層表(INNER TABLE)。在我們從驅動表中得
到具體一行的資料後，在該表中尋找符合連線條件的行。所以該表應當為大表(實際
上應該為返回較大row source的表)且相應的列上應該有索引。在我們後面的描述中，
一般將該表稱為連線操作的row source 2。

組合索引(concatenated index)：由多個列構成的索引，如create index idx_emp
on emp(col1, col2, ol3, ……)，則我們稱idx_emp索引為組合索引。在組合索引
中有一個重要的概念：引導列(leading column)，在上面的例子中，col1列為引導
列。當我們進行查詢時可以使用”where col1 = ? ”，也可以使用”where col1 = ?
and col2 = ?”，這樣的限制條件都會使用索引，但是”where col2 = ? ”查詢就
不會使用該索引。所以限制條件中包含先導列時，該限制條件才會使用該組合索引。

可選擇性(selectivity)：比較一下列中唯一鍵的數量和表中的行數，就可以判斷該
列的可選擇性。如果該列的”唯一鍵的數量/表中的行數”的比值越接近1，則該列
的可選擇性越高，該列就越適合建立索引，同樣索引的可選擇性也越高。在可選擇
性高的列上進行查詢時，返回的資料就較少，比較適合使用索引查詢。

二．oracle訪問資料的存取方法

1) 全表掃描（Full Table Scans, FTS）

為實現全表掃描，Oracle讀取表中所有的行，並檢查每一行是否滿足語句的WHERE
限制條件,一個多塊讀操作可以使一次I/O能讀取多塊資料塊(db_block_multiblock_read_count
引數設定)，而不是隻讀取一個資料塊，這極大的減少了I/O總次數，提高了系統的吞
吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描，而且只有在全表掃描的
情況下才能使用多塊讀操作。在這種訪問模式(FTS)下，每個資料塊只被讀一次。

使用FTS的前提條件：在較大的表上不建議使用全表掃描，除非取出資料的比較多，
超過總量的5% -- 10%，或你想使用並行查詢功能時。

使用全表掃描的例子：

SQL> explain plan for select * from dual;
Query Plan-------------------------------
SELECT STATEMENT[CHOOSE]
Cost=TABLE ACCESS FULL DUAL

 

2) 透過ROWID的表存取（Table Access by ROWID或rowid lookup）

行的ROWID指出了該行所在的資料檔案、資料塊以及行在該塊中的位置，所
以透過ROWID來存取資料可以快速定位到目標資料上，是Oracle存取單行資料
的最快方法。

這種存取方法不會用到多塊讀操作，一次I/O只能讀取一個資料塊。我們會
經常在執行計劃中看到該存取方法，如透過索引查詢資料。

使用ROWID存取的方法：

SQL> explain plan for select * from dept
where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

 

3）索引掃描（Index Scan或index lookup）

我們先透過index查詢到資料對應的rowid值(對於非唯一索引可能返回多個
rowid值)，然後根據rowid直接從表中得到具體的資料(ROWID指出了該行所
在的資料檔案、資料塊以及行在該塊中的位置)，這種查詢方式稱為索引掃
描或索引查詢(index lookup)。一個rowid唯一的表示一行資料，該行對應
的資料塊是透過一次I/O得到的，在此情況下該次I/O只會讀取一個資料庫塊。

在索引中，除了儲存每個索引的值外，索引還儲存具有此值的行對應的ROWID
值。索引掃描可以由2步組成：  
(1) 掃描索引得到對應的rowid值(對索引的一次I/O)。
(2) 透過rowid從表中讀出具體的資料(讀取資料的一次I/O)。

每步都是單獨的一次I/O，但是對於索引，由於經常使用，絕大多數都已經
CACHE到記憶體中，所以第1步的I/O(這裡指標對INDEX的I/O)經常是邏輯I/O，
即資料可以從記憶體中得到。但是對於第2步來說，如果表比較大，則其資料
不可能全在記憶體中，所以其I/O很有可能是物理I/O，這是一個機械操作，相
對邏輯I/O來說，是極其費時間的。所以如果大表進行索引掃描，取出的
資料如果大於總量的5% -- 10%，使用索引掃描會效率下降很多。如下列所示：

SQL> explain plan for select empno,
ename from emp where empno=10;
Query Plan-------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

但是如果查詢的資料能全在索引中找到，就可以避免進行第2步操作，避免了
不必要的I/O，此時即使透過索引掃描取出的資料比較多，效率還是很高的
(當然這種情況下比較有用的使用是透過聯合索引的第一個引導列查詢出其
他非索引引導列)。

SQL> explain plan for select empno from emp where empno=10;
-- 只查詢empno列值
Query Plan------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1INDEX UNIQUE SCAN EMP_I1

進一步講，如果sql語句中對索引列進行排序，因為索引已經預先排序好了，
所以在執行計劃中不需要再對索引列進行排序

SQL> explain plan for select empno,
ename from empwhere empno > 7876 order by empno;
Query Plan--------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

從這個例子中可以看到：因為索引是已經排序了的，所以將按照索引的
順序查詢出符合條件的行，因此避免了進一步排序操作。

根據索引的型別與where限制條件的不同，有4種型別的索引掃描：

索引唯一掃描(index unique scan)
索引範圍掃描(index range scan)
索引全掃描(index full scan)
索引快速掃描(index fast full scan)

(1) 索引唯一掃描(index unique scan)

透過唯一索引查詢一個數值經常返回單個ROWID。如果存在UNIQUE 或
PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經常實
現唯一性掃描。

使用唯一性約束的例子：

SQL> explain plan for
select empno,ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

(2) 索引範圍掃描(index range scan)

使用一個索引存取多行資料，在唯一索引上使用索引範圍掃描的典型
情況下是在謂詞(where限制條件)中使用了範圍運算子(如>、、
>=、<=、between)

使用索引範圍掃描的例子：

SQL> explain plan for select empno,
ename from emp where empno > 7876 order by empno;
Query Plan-------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

在非唯一索引上，謂詞col = 5可能返回多行資料，所以在非唯一
索引上都使用索引範圍掃描。

使用index rang scan的3種情況：
(a) 在唯一索引列上使用了range運算子(> < <> >= <= between)
(b) 在組合索引上，只使用部分列進行查詢，導致查詢出多行
(c) 對非唯一索引列上進行的任何查詢。

(3) 索引全掃描(index full scan)

與全表掃描對應，也有相應的全索引掃描。而且此時查詢出的資料都
必須從索引中可以直接得到。

全索引掃描的例子：

An Index full scan will not perform. single block
i/o's and so it may prove to be inefficient.

e.g.Index BE_IX is a concatenated index on big_emp (empno, ename)

SQL> explain plan for select empno, ename from big_emp order
by empno,ename;
Query Plan--------------------------
SELECT STATEMENT[CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]

(4) 索引快速掃描(index fast full scan)

掃描索引中的所有的資料塊，與 index full scan很類似，但是一個
顯著的區別就是它不對查詢出的資料進行排序，即資料不是以排序順序
被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用並行
讀入，以便獲得最大吞吐量與縮短執行時間。

索引快速掃描的例子：

BE_IX索引是一個多列索引：

big_emp (empno,ename)
SQL> explain plan for select empno,
ename from big_emp;
Query Plan--------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

只選擇多列索引的第2列：

SQL> explain plan for select ename from big_emp;
Query Plan--------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

 

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

Index Full Scan (索引全掃描)    --------- 

當進行index full scan的時候，oracle定位到索引的root block，
然後到branch block（假如有的話），再定位到第一個leaf block, 然後
根據leaf block的雙向連結串列順序讀取。它所讀取的塊都是有順序的，也是
經過排序的，所以index full scan可以用來避免某些sort操作。查詢出的
資料都必須從索引中可以直接得到。   這個full scan的名字有點誤導人，
其實並不是所有的index block都被讀取的，某些分支塊是不會讀到的。

1.   查詢出來的資料都必須從Index中可以直接得到。  
2.   Index中至少有一個欄位是非空 。 
3.   可以消除掉排序操作，因為資料已經被index key 排好序了 。 
4.   它一個一個地讀取block,  不能多塊讀 。也不能並行。

 

Index Fast Full Scan (索引快速全掃描)  -----  
而index fast full scan則不同，它是從段頭開始，讀取包含點陣圖塊，
root block, 所有的branch block, leaf block，讀取的順序完全由物理存
儲位置決定，並採取多塊讀，每次讀取db_file_multiblock_read_count個塊。
因為能並行或多塊讀，index fast full scan不能用來消除sort操作。 

1.   這種Index Fast Full Scan 只有在CBO下有效
2.   需要分析Index , 否則最佳化程式可能不會用它
3.    在Index中取資料，而不是存取table中的資料
4.    Index中至少一個欄位是非空
5.    不能消除排序操作， 因為資料沒排序
6.    與Index Full Scan不一樣，用多塊讀方式讀整個Index 
7.    Index Fast Full Scan 比 正常的Index Full Scan 更快，因為能用多塊讀方式，且能並行執行
8.     Index Fast Full Scan 並行和表的並行是分開的， 可以透過Parallel 來設定