讀書筆記-《基於Oracle的SQL優化》-第一章-3

bisal發表於2013-12-15
筆記OracleSQL優化
優化器：
1、優化器的模式：
用於決定在Oracle中解析目標SQL時所用優化器的型別，以及決定當使用CBO時計算成本值的側重點。這裡的“側重點”是指當使用CBO來計算目標SQL各條執行路徑的成本值時，計算成本值的方法會隨著優化器模式的不同而不同。
Oracle中，優化器的模式是由引數OPTIMIZER_MODE的值來決定的。
RULE：表示Oracle將使用RBO來解析目標SQL，此時SQL中涉及的各個物件的統計資訊對於RBO沒有任何作用。
CHOOSE：Oracle 9i的預設值，表示RBO還是CBO取決於SQL涉及的表物件是否有統計資訊。
FIRST_ROWS_n(n=1,10,100,1000)：此時CBO計算SQL的各條執行路徑的成本值時的側重點在於以最快的響應速度返回頭n(n=1,10,100,1000)條記錄。
FIRST_ROWS：Oracle 9i中就已經過時的引數，當一些特殊情況下的時候，會使用RBO中的一些內建的規則來選取執行計劃不再考慮成本。例如當發現能用相關的索引來避免排序，則會選擇索引對應的執行路徑不再考慮成本，顯然是不合理的。這時，索引全掃描的概率比以前有所增加，因為用索引全掃描能避免排序。
ALL_ROWS：Oracle 10g及以後版本中OPTIMIZER_MODE的預設值，表示使用CBO解析目標SQL，此時CBO計算SQL的各條執行路徑的成本值時的側重點在於最佳的吞吐量(即最小的系統I/O和CPU資源的消耗量)。

2、結果集：
指包含指定執行結果的集合。對RBO來說，對應的執行計劃中沒有對相關執行步驟對應的結果集的描述，雖然結果集的概念對RBO也是適用的。對CBO來說，對應執行計劃中的列(Rows)反映的就是CBO對應相關執行步驟所對應的輸出結果集的記錄數(Cardinality)的估算值。

3、訪問資料的方法：
3.1 訪問表的方法
全表掃描：
指Oracle訪問目標表裡的資料時，會從該表所佔用的第一個區(Extent)的第一個塊(Block)開始掃描，一直掃描到該表的高水位線(HWM)，這段範圍內所有的資料塊都必須讀到。
ROWID掃描：
指Oracle訪問目標表裡的資料時，直接通過資料所在的ROWID定位並訪問這些資料。ROWID表示Oracle中的資料行記錄所在的物理儲存地址，也就是說ROWID實際上和Oracle中資料塊裡的行記錄一一對應的。
ROWID掃描有兩層含義：一種是根據使用者在SQL語句中輸入的ROWID的值直接訪問對應的資料行記錄；另外一種是先訪問相關的索引，然後根據訪問索引後得到的ROWID再回表訪問對應的資料行記錄。
對Oracle堆表而言，通過Oracle內建的ROWID偽列得到對應航記錄所在的ROWID的值(注意：ROWID只是一個偽列，在實際的表塊中並不存在該列)，然後還可以根據DBMS_ROWID包中的相關方法(dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和dbms_rowid.rowid_row_number)將上述ROWID偽列的值翻譯成對應資料行的實際物理儲存地址。
訪問索引的方法：
(1)、索引唯一性掃描：INDEX UNIQUE SCAN，僅適用於where條件中是等值查詢的目標SQL。因為掃描的物件是唯一性索引，所以索引唯一性掃描的結果至多隻會返回一條記錄。
(2)、索引範圍掃描：INDEX RANGE SCAN，當掃描的物件是唯一性索引時，目標SQL的where條件一定是範圍查詢(謂詞條件為BETWEEN、等)；當掃描的物件是非唯一性索引時，對目標SQL的where條件沒有限制(可以是等值查詢，也可以是範圍查詢)。
在同等條件下，當目標索引的索引行的數量大於1時，索引範圍掃描所耗費的邏輯讀至少會比相應的索引唯一性掃描多1。
(3)、索引全掃描：指要掃描目標索引所有葉子塊的所有索引行。但並不意味著需要掃描該索引的所有分支塊。預設情況下，Oracle在做索引全掃描時只需要通過訪問必要的分支塊定位到位於該索引最左邊的葉子塊的第一行索引行，就可以利用該索引葉子塊之間的雙向指標連結串列，從左至右依次順序掃描該索引所有葉子塊的所有索引行了。
按照索引鍵值順序排序，即可達到排序的效果。避免真正的排序。
預設情況下，索引全掃描的有序性就決定了所以全掃描不能並行執行，通常使用單塊讀。
做索引全掃描的前提條件是目標索引至少有一個索引鍵值列的屬性是NOT NULL。
索引快速全掃描：INDEX FAST FULL SCAN，需要掃描目標索引所有葉子塊的所有索引行。
與索引全掃描的區別：
（1）、索引快速全掃描只適用於CBO。
（2）、索引快速全掃描可以使用多塊讀，也可以並行執行。
（3）、索引快速全掃描結果不一定是有序的。因為索引快速全掃描時Oracle是根據索引行在磁碟上的物理儲存順序來掃描，而不是根據索引行的邏輯順序來掃描的。所以掃描結果才不一定有序（對於單個索引葉子塊中的索引行而言，其物理儲存順序和邏輯儲存順序一致，但對於物理儲存位置相鄰的索引葉子塊而言，塊與塊之間索引行的物理儲存順序則不一定在邏輯上有序。
索引跳躍式掃描：INDEX SKIP SCAN，它使那些在where條件中沒有對目標索引的前導列指定查詢條件但同時又對該索引的非前導列指定了查詢條件的目標SQL依然可以用上該索引，這就像在掃描該索引時跳過了它的前導列。這是因為Oracle幫你對該索引的前導列的所有distinct值做了遍歷。
Oracle中的索引跳躍式掃描僅適用於那些目標索引前導列的distinct值數量較少，後續非前導列的可選擇性又非常好的情形，因為索引跳躍式掃描的執行效率一定會隨著目標索引前導列的distinct值數量的遞增而遞減。

表連線
當優化器解析含表連線的目標SQL時，它除了會根據目標SQL的SQL文字的寫法來決定表連線的型別之外，還必須決定如下三件事情才能得到最終的執行計劃。
（1）、表連線順序
（2）、表連線方法
（3）、訪問單表的方法

表連線型別：
（1）、內連線
只要where條件中沒有寫那些標準SQL中定義或者Oracle中自定義的表示外連線的關鍵字，則該SQL的連線型別就是內連線。
標準SQL中內連線的寫法是用JOIN ON或者JOIN USING。
目標表1 join 目標表2 on （連線條件）
目標表1 join 目標表2 using （連線列集合）
注意：對於使用JOIN USING的標準SQL而言，如果連線列同時又出現在查詢列中，則該連線列前不能帶上表名或者表名的別名（alias），否則Oracle會報錯（ORA－25154）。
特殊的JOIN USING，我們稱之為NATURAL JOIN，使用NATURAL JOIN的表連線的連線列是表連線的兩個表所有的同名列。
目標表1 natural join 目標表2
相當於：目標表1 join 目標表2 using （目標表1和目標表2的所有同名列集合）
（2）、外連線
左連線：目標表1 left outer join 目標表2 on （連線條件）
或
目標表1 left outer join 目標表2 u?si?n?g （連線列集合）
left outer join左邊的目標表1作為表連線的驅動表，即表明位置處於left的表就是outer table，驅動表。此時連線結果除了包含目標表1和目標表2中所有滿足該連線條件的記錄外，還會包含驅動表（目標表1）中所有不滿足該連線條件的記錄，同時，驅動表中所有不滿足該連線條件的紀錄所對應的被驅動表（目標表2）中的查詢列均會以NULL值來填充。
右連線：目標表1 right outer join 目標表2 on （連線條件）
或
目標表1 right outer join 目標表2 using （連線列集合）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-1063319/，如需轉載，請註明出處，否則將追究法律責任。

相關文章