Oracle sql最佳化必知——表的訪問

標籤：oracle最佳化   sql最佳化基礎   訪問表   

《訪問資料的方法》

訪問表中的資料有兩種：1、直接存取表   2、先訪問索引，再回表

1、直接存取表的兩種方法：

①、全表掃描 

    全表掃描是指Oracle在訪問目標表的資料時，會從該表所佔用的第一個區（extent）的第一個塊（block）開始掃描，一直掃描到該表的高水位線，這段範圍內的所有資料庫都必須讀到，當然如果目標sql的where中指定的過濾條件，最後只返回滿足條件的資料即可；（有時候全表掃描的效率還是非常高的，但是隨著表的資料增多 資源消耗也會在逐步增加）

②、rowid掃描

    rowid掃描是指Oracle在訪問目標表裡的資料時，直接通過資料所在的rowid去定位並訪問這些資料。rowid表示的是Oracle中的資料行記錄所在的實體儲存體地址，也就是說rowid實際上是和Oracle中資料區塊裡的行記錄一一對應的。

    Oracle中的rowid掃描有兩層含義：

    一種是根據使用者在sql語句中輸入的rowid的值直接存取對應的資料行記錄；

    另一種是先去訪問相關的索引，然後根據訪問索引後得到的rowid再回表去訪問對應的資料行記錄。

2、訪問索引的方法

   常用的是B樹索引，優點如下：

   ①：所有的索引葉子塊都在同一層，即他們距離索引根節點的深度是相同的，這也意味著訪問索引葉子塊的任何一個索引索引值所花費的時間幾乎相同。

   ②：Oracle會保證所有的B樹索引都是自平衡的，即不可能出現不同的索引葉子塊不處同一層的現象。

   ③：通過B樹索引訪問表裡行記錄的效率並不會隨著相關表的資料量遞增而顯著降低，即通過走索引訪問資料的時間是可控的，基本穩定的，這也是走索引和全表掃描的最大區別；

 

 一些常見的訪問B樹索引的方法：

 ①：索引唯一性掃描（index unique scan）：

    索引唯一性掃描是針對唯一性索引的掃描，它僅適用於where條件裡是等值查詢的目標sql。因為掃描的對象是唯一性索引，所以索引唯一性掃描的結果至多隻會返回一條記錄。

 ②：索引範圍掃描（index range scan）     

    索引範圍掃描適用於所有類型的B樹索引，當掃描的對象是唯一性索引時，此時目標sql的where條件一定是範圍查詢；要注意即使是針對同等條件下的相同的sql，當目標索引的數量大於1時，索引範圍掃描所耗費的邏輯讀會多於索引唯一性掃描所耗費的邏輯讀。

 ③：索引全掃描（index full scan） 

    索引全掃描適用於所有類型的B樹索引（包括唯一性索引和非唯一性索引）。是指要掃描目標索引所有葉子塊的所有索引行。

    預設情況下，Oracle在做索引全掃描時只需要通過訪問必要的分支塊定位到位於該索引最左邊的葉子塊的第一行索引行；

    說明：索引全掃描的執行結果也是有序的，並且是按照該索引的索引索引值列來排序，這也意味著走索引全掃描能夠即達到排序的

    效果，又同時避免了該索引的索引索引值列達的真正排序操作。

 ④：索引快速全掃描（index fast full scan）

    索引快速全掃描和索引全掃描極為類似，它也是由於所有類型的B樹索引（包括唯一性索引和非唯一性索引）。和索引全掃描一樣，索引快速全掃描也需要掃描目標索引所有葉子塊的所有索引行；

索引快速全掃描與索引全掃描的區別如下：

    ①：索引快速全掃描只適用CBO

    ②：索引快速全掃描可以使用多塊讀，也可以並存執行

    ③：索引快速全掃描的執行結果不一定是有序的。

    

例句：如下帶hint的目標sql是讓Oracle走對主鍵索引pk_emp_test的索引快速全掃描

    (emp_test是表名   pk_emp_test是表的主鍵複合索引  empno是查詢的欄位)

    select /*+ index_ffs(emp_test pk_emp_test) */empno from emp_test;

 

 ⑤：索引跳躍式掃描（index skip scan）

   索引跳躍式掃描適用所有類型的複合B樹索引（包括唯一性索引和非唯一性索引），它使那些在where條件中沒有對目標索引的前置列指定查詢條件

   但同時對該索引的非前置列指定了查詢條件的sql依然可以用上該索引。

   

注意：

   Oracle中的索引跳躍式掃描僅適用於那些目標索引前置列的distinct值數量較少、後續非前置列的可選擇性又非常好的圖形，因為索引跳躍式掃描的執行

   效率一定會隨著目標索引前置列的distinct值數量的遞增而遞減。     

清空資料字典緩：--生產庫禁用

SQL> alter system flush shared_pool;

清空buffer cache緩衝：---生產庫禁用

SQL> alter system flush buffer_cache;

《表串連》

1、表串連順序

   不管目標sql有多少個表做表串連，Oracle在實際執行該sql時都只能先兩兩做表串連，再一次執行這樣的兩兩表串連過程，直到目標sql中所有的表都已經串連完畢；

   

2、表串連方法：

   兩個表串連的方法有：排序合并串連、嵌套迴圈串連、雜湊串連、笛卡爾積串連 四種；

3、表串連的類型：

 3.1內串連：

   內串連是指表串連的串連結果只包含那些完全滿足串連條件的記錄。對於包含表串連的目標sql而言，只要起where條件中沒有寫那些標準sql中定義或者Oracle中自訂的表示外串連的關鍵字（比如標準sql中的left outer join、right outer join、full outer join，或者Oracle中自訂的用來表示外串連的關鍵字“+”），則該sql的連線類型就是內串連。

   如： select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2; 這條sql就沒有那些關鍵字，這就是內串連

   標準sql中的內串連寫法是用：join on   或  join using  （natural join 特殊的串連方法）

  

 join  on 文法：目標表1  join  目標表2  on  （串連條件）

       如：select t1.col1,t1.col2,t2.col3 from t1 join t2 on (t1.col2=t2.col2);

 

join  using 文法：目標表1  join  目標表2  using （串連列集合）

       如：select t1.col1,col2,t2.col3 from t1 join t2 using (col2);

注意：對於使用join using的標準sql而言，如果串連列同時又出現在查詢列中，則該串連列前不能帶上表名或者表名的別名，否則Oracle會報錯（ORA-25154）

    

natural join文法：目標表1  natural  join  目標表2 

       如：select t1.col1,col2,t2.col3 from t1 natural join t2;

 

注意：

   對於內串連而言，除了表串連條件之外的額外限制條件在目標sql的sql文本中所處的位置並不會影響該sql的實際執行結果；

 3.2、外串連（outer join）

    外串連 是對內串連的一種擴充，它是指表串連的串連結果除了包含那些完全滿足串連條件的記錄之外還會包含驅動表中所有不滿足該串連條件的記錄。

    外串連分為：左串連（left outer join ）  右串連（right outer join ）和全串連（full outer join）

    

    左串連的文法：（關鍵字左邊的就是驅動表，即目標表1就是驅動表）

      左串連的查詢結果除了包含目標表1和目標表2中所有滿足該串連條件的記錄外，還包含驅動表中所有不滿足該串連條件的記錄，同時，驅動表中所有不滿足該串連條件的記錄所對應的被驅動表中的查詢列均會以null值來填充。

    目標表1  left outer join  目標表2  on （串連條件）  或  目標表1  left outer join 目標表2  using （串連列集合）

    

    右串連的文法：（關鍵字右邊的就是驅動表，即目標表2就是驅動表） 

       右串連的查詢結果除了包含目標表1和目標表2中所有滿足該串連條件的記錄外，還包含驅動表中所有不滿足該串連條件的記錄，同時，驅動表中所有不滿足該串連條件的記錄所對應的被驅動表中的查詢列均會以null值來填充。

    目標表1  right  outer join   目標表2  on （串連條件）  或  目標表1  right outer join 目標表2 using（串連列集合）

    

    全串連的文法：

    全串連的查詢結果除了包含目標表1和目標表2中所有滿足該串連條件的記錄外，還包含目標表1和目標表2中所有不滿足該串連條件的記錄，同時，驅動表中所有不滿足該串連條件的記錄所對應的被驅動表中的查詢列均會以null值來填充。

    目標表1  full outer join 目標表2 on （串連條件）  或  目標表1  full outer join 目標表2 using （串連列集合）

    

注意：

   對於外串連而言，除了表串連條件之外的額外限制條件在目標sql的sql文本中所處的位置確實可能會影響該sql的實際執行結果。

   Oracle自訂的關鍵字“（+）”來表示外串連：

      關鍵字“（+）”出現在哪個表的串連列後面，就表名哪個表會以null值來填充那些不滿足串連條件並位於該表中的查詢列，

      此時應該以關鍵字“（+）”對面的表來作為外串連的驅動表，這裡的關鍵是決定哪個表是驅動表；

4、表串連的方法：

   在Oracle資料庫中有四種表串連方法：排序合并串連、嵌套迴圈串連、雜湊串連和笛卡爾串連      

4.1、排序合并串連（sort merge join）

    排序合并串連是一種兩個表在做表串連時用排序操作（sort）和合併作業（merge）來得到串連結果集的表串連方法；

    

    排序合并串連的優缺點及適用情境：

    ①：通常情況下，排序合并串連的執行效率會遠不如雜湊串連，但前者的使用範圍更廣，因為雜湊串連通常只能用於等值串連條件，

        而排序合并串連還能用於其他串連條件（例如：<  <=  >  >=）

    ②：通常情況下，排序合并串連並不適合OLTP類型的系統，因為對於OLTP類型的系統而言，排序是非常昂貴的操作，當然，如果能

        避免排序操作，那麼即使是OLTP類型的系統，也還是可以使用排序合并串連的。

    ③：從嚴格意義上來說，排序合并串連並不存在驅動表的概念；

 

4.2嵌套迴圈串連（nested loops join）

   嵌套迴圈串連是一種兩個表在做表串連時依靠兩層嵌套迴圈（分別為外層迴圈和內層迴圈）來得到串連結果集的表串連方法。

   

   嵌套迴圈串連的優缺點及適用情境：

   ①：如果驅動表所對應的驅動結果集的記錄較少，同時在被驅動表的串連列上又存在唯一性索引（或者在被驅動表的串連列上存在選擇性很好的

   非唯一性索引），那麼此時使用嵌套迴圈串連的執行效率就會非常高。但如果驅動表所對應的驅動結果集的記錄數很多，即便在被驅動表的串連

   列上存在索引，此時使用嵌套迴圈串連的執行效率也不會高。

   ②：大表可以作為嵌套迴圈串連的驅動表，關鍵看目標sql中指定的謂詞條件（如果有的話）能否將驅動結果集的資料量降下來；

   ③：嵌套迴圈串連有其他串連方法所沒有的一個優點：嵌套迴圈串連可以實現快速響應。

 

4.3、雜湊串連（hash join）

   雜湊串連是一種兩個表在做串連時主要依靠雜湊運算來得到串連結果集的表串連方法；

   注意：雜湊串連只適用於CBO

         從理論上來說，雜湊串連的執行效率會比排序合并串連和嵌套迴圈串連要高，當然，實際情況並不總是這樣。

    雜湊串連的優缺點及適用情境：

    ①：雜湊串連不一定會排序，或者說大多數情況下都不需要排序

    ②：雜湊串連的驅動表所對應的串連列的可選擇性應儘可能好，因為這個可選擇性會影響對應hash bucket中的記錄數，

        而hash bucket中的記錄數又會直接影響從該 hash bucket中尋找匹配記錄的效率。

    ③：雜湊串連只適用於CBO，它也只能用於等值串連條件（即使是雜湊反串連，Oracle實際上也是將其轉換成了等價的等值串連）

    ④:雜湊串連很適合於小表和大表之間做表串連且串連結果集的記錄數較多的情形，特別是在小表的串連列的可選擇性非常好的情況下，

       這時候雜湊串連的執行時間就可以近似看作是全表掃描那個大表所耗費的時間相當；

    ⑤：當兩個表做雜湊串連時，如果在施加了目標sql中指定的謂詞條件（如果有的話）後得到的資料量較小的那個結果集所對應的hash table

        能夠完全被容納在記憶體中（PGA的工作區），則此時的雜湊串連的執行效率會非常高；

 

 4.4、笛卡爾串連（cross join）

    笛卡爾串連又稱為笛卡爾乘積，它是一種兩個表在做表串連時沒有任何串連條件的表串連方法。

    

    注意：笛卡爾串連實際上就是一種特殊的合并串連，這裡的合并串連和排序合并串連類似，只不過笛卡爾串連不需要排序，並且在

          執行合併作業時沒有串連條件而已。              

反串連：（anti  join）

   反串連是一種特殊的連線類型，與內串連和外串連不同，Oracle資料庫裡並沒有相關的關鍵字可以在sql文本中專門表示反串連；

   

   注意：當做子查詢展開時，Oracle經常會把那些外部where條件為 not exists  not in 或<>all 的子查詢轉換成對應的反串連；

半串連：（semi  join）

  半串連是一種特殊的連線類型，與反串連一樣，Oracle資料庫裡也沒有相關的關鍵字可以在sql文本中專門表示半串連；

  

  注意：當做子查詢時，Oracle經常會把那些外部where條件為exists  in  或 =any的子查詢轉換為對應的半串連；

4.5、星型串連（star join）

   星型串連通常用於資料倉儲類型的應用，它是一種單個事實表（fact table）和多個維度資料表之間的串連；

------------------------------參考《基於Oracle的sql最佳化》---------------------------------

本文出自 “笨小孩的dba之路” 部落格，請務必保留此出處http://fengfeng688.blog.51cto.com/4896812/1953300

Oracle sql最佳化必知——表的訪問