程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle優化查詢中所用到的語句有哪些?

Oracle優化查詢中所用到的語句有哪些?

編輯:Oracle數據庫基礎

以下的文章主要講述的是Oracle優化查詢的實際操作我們首先是假設LARGE_TABLE是一個表(較大型),而且username列上是沒有相關索引的,那麼它在實際的操作中要用到的語句有那些?下面就是對其語句的描述:

  1. SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST';   
  2. Query Plan   
  3. SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)   
  4. TABLE Access FULL LARGE_TABLE [:Q65001] [ANALYZED] 

在這個例子中,TABLE Access FULL LARGE_TABLE是第一個操作,意思是在LARGE_TABLE表上做全表掃描。當這個操作完成之後,產生的row source中的數據被送往下一步驟進行處理,在此例中,SELECT STATEMENT操作是這個Oracle優化查詢語句的最後一步。

Optimizer=CHOOSE 指明這個查詢的optimizer_mode,即optimizer_mode初始化參數指定的值,它並不是指語句執行時真的使用了該優化器。決定該語句使用何種優化器的唯一方法是看後面的cost部分。例如,如果給出的是下面的形式,則表明使用的是CBO優化器,此處的cost表示優化器認為該執行計劃的代價:

  1. SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14

然而假如執行計劃中給出的是類似下面的信息,則表明是使用RBO優化器,因為cost部分的值為空,或者壓根就沒有cost部分。

  1. SELECT STATEMENT Optimizer=CHOOSE Cost=   
  2. SELECT STATEMENT Optimizer=CHOOSE 

這樣我們從Optimizer後面的信息中可以得出執行該語句時到底用了什麼樣的優化器。特別的,如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n,則使用的是CBO優化器;如果Optimizer=RULE,則使用的是RBO優化器。

cost屬性的值是一個在Oracle內部用來比較各個執行計劃所耗費的代價的值,從而使優化器可以選擇最好的執行計劃。不同語句的cost值不具有可比性,只能對同一個語句的不同執行計劃的cost值進行比較。

[:Q65001] 表明該部分查詢是以並行方式運行的。裡面的數據表示這個操作是由並行Oracle優化查詢的一個slave進程處理的,以便該操作可以區別於串行執行的操作。

[ANALYZED] 表明操作中引用的對象被分析過了,在數據字典中有該對象的統計信息可以供CBO使用。

例2:

假定A、B、C都是不是小表,且在A表上一個組合索引:A(a.col1,a.col2) ,注意a.col1列為索引的引導列。考慮下面的查詢:

  1. select A.col4   
  2. from A , B , C   
  3. where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5   
  4. Execution Plan   
  5. SELECT STATEMENT Optimizer=CHOOSE   
  6. 0  MERGE JOIN   
  7. 1 SORT (JOIN)   
  8. 2  NESTED LOOPS   
  9. 3 TABLE Access (FULL) OF 'B'   
  10. 3 TABLE Access (BY INDEX ROWID) OF 'A'   
  11. 5  INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
  12. 1 SORT (JOIN)   
  13. 7  TABLE Access (FULL) OF 'C'  
  14. Statistics   
  15. 0 recursive calls   
  16. 8 db block gets   
  17. 6 consistent gets   
  18. 0 physical reads   
  19. 0 redo size   
  20. 551  bytes sent via SQL*Net to clIEnt   
  21. 430  bytes received via SQL*Net from clIEnt   
  22. 2 SQL*Net roundtrips to/from clIEnt   
  23. 2 sorts (memory)   
  24. 0 sorts (disk)   
  25. 6 rows processed  

在表做連接時,只能2個表先做連接,然後將連接後的結果作為一個row source,與剩下的表做連接,在上面的例子中,連接順序為B與A先連接,然後再與C連接:
B  <--->  A  <--->  C
col3=10  col3=5

如果沒有執行計劃,分析一下,上面的3個表應該拿哪一個作為第一個驅動表?從SQL語句看來,只有B表與C表上有限制條件,所以第一個驅動表應該為這2個表中的一個,到底是哪一個呢?

B表有謂詞B.col3 = 10,這樣在對B表做全表掃描的時候就將where子句中的限制條件(B.col3 = 10)用上,從而得到一個較小的row source, 所以B表應該作為第一個驅動表。而且這樣的話,如果再與A表做關聯,可以有效利用A表的索引(因為A表的col1列為leading column)。

當然上面的Oracle優化查詢中C表上也有謂詞(C.col3 = 5),有人可能認為C表作為第一個驅動表也能獲得較好的性能。讓我們再來分析一下:如果C表作為第一個驅動表,則能保證驅動表生成很小的row source,但是看看連接條件A.col2 = C.col2,此時就沒有機會利用A表的索引,因為A表的col2列不為leading column,這樣nested loop的效率很差,從而導致查詢的效率很差。

所以對於NL連接選擇正確的驅動表很重要。

因此上面查詢比較好的連接順序為(B - - > A) - - > C。如果數據庫是基於代價的優化器,它會利用計算出的代價來決定合適的驅動表與合適的連接順序。一般來說,CBO都會選擇正確的連接順序,如果CBO選擇了比較差的連接順序,我們還可以使用Oracle提供的hints來讓CBO采用正確的連接順序。如下所示:

  1. select /*+ ordered */ A.col4   
  2. from B,A,C   
  3. where B.col3 = 10   
  4. and A.col1 = B.col1   
  5. and A.col2 = C.col2   
  6. and C.col3 = 5 

既然選擇正確的驅動表這麼重要,那麼讓我們來看一下執行計劃,到底各個表之間是如何關聯的,從而得到執行計劃中哪個表應該為驅動表:在執行計劃中,需要知道哪個操作是先執行的,哪個操作是後執行的,這對於判斷哪個表為驅動表有用處。

判斷之前,如果對表的訪問是通過rowid,且該rowid的值是從索引掃描中得來得,則將該索引掃描先從執行計劃中暫時去掉。然後在執行計劃剩下的部分中,判斷執行順序的指導原則就是:最右、最上的操作先執行。具體解釋如下:

得到去除妨礙判斷的索引掃描後的執行計劃:

  1. Execution Plan    
  2. SELECT STATEMENT Optimizer=CHOOSE   
  3. 0  MERGE JOIN   
  4. 1 SORT (JOIN)   
  5. 2  NESTED LOOPS   
  6. 3 TABLE Access (FULL) OF 'B'   
  7. 3 TABLE Access (BY INDEX ROWID) OF 'A'   
  8. 1 SORT (JOIN)   
  9. 7  TABLE Access (FULL) OF 'C' 

看執行計劃的第3列,即字母部分,每列值的左面有空格作為縮進字符。在該列值左邊的空格越多,說明該列值的縮進越多,該列值也越靠右。如上面的執行計劃所示:第一列值為6的行的縮進最多,即該行最靠右;第一列值為4、5的行的縮進一樣,其靠右的程度也一樣,但是第一列值為4的行比第一列值為5的行靠上;談論上下關系時,只對連續的、縮進一致的行有效。

從這個圖中我們可以看到,對於NESTED LOOPS部分,最右、最上的操作是TABLE Access (FULL) OF 'B',所以這一操作先執行,所以該操作對應的B表為第一個驅動表(外部表),自然,A表就為內部表了。

從圖中還可以看出,B與A表做嵌套循環後生成了新的row source ,對該row source進行來排序後,與C表對應的排序了的row source(應用了C.col3 = 5限制條件)進行MSJ連接操作。所以從上面可以得出如下事實:B表先與A表做嵌套循環,然後將生成的row source與C表做排序—合並連接。

通過分析上面的執行計劃,我們不能說C表一定在B、A表之後才被讀取,事實上,B表有可能與C表同時被讀入內存,因為將表中的數據讀入內存的操作可能為並行的。事實上許多操作可能為交叉進行的,因為Oracle讀取數據時,如果就是需要一行數據也是將該行所在的整個數據塊讀入內存,而且還有可能為多塊讀。

看執行計劃時,我們的關鍵不是看哪個操作先執行,哪個操作後執行,而是關鍵看表之間連接的順序(如得知哪個為驅動表,這需要從操作的順序進行判斷)、使用了何種類型的關聯及具體的存取路徑(如判斷是否利用了索引)

在從執行計劃中判斷出哪個表為驅動表後,根據我們的知識判斷該表作為驅動表(就像上面判斷ABC表那樣)是否合適,如果不合適,對SQL語句進行更改,使優化器可以選擇正確的驅動表。

對於RBO優化器:

在Oracle文檔上說:對於RBO來說,以from 子句中從右到左的順序選擇驅動表,即最右邊的表為第一個驅動表,這是其英文原文:All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。

不過,在我做的測試中,從來也沒有驗證過這種說法是正確的。我認為,即使在RBO中,也是有一套規則來決定使用哪種連接類型和哪個表作為驅動表,在選擇時肯定會考慮當前索引的情況,還可能會考慮where 中的限制條件,但是肯定是與where中限制條件的位置無關。

測試 :如果我創建3個表:

  1. create table A(col1 number(4,0),col2 number(4,0), col4 char(30));   
  2. create table B(col1 number(4,0),col3 number(4,0), name_b char(30));   
  3. create table C(col2 number(4,0),col3 number(4,0), name_c char(30));   
  4. create index inx_col12A on a(col1,col2); 

執行Oracle優化查詢:

  1. select A.col4   
  2. from B, A, C   
  3. where B.col3 = 10   
  4. and A.col1 = B.col1   
  5. and A.col2 = C.col2   
  6. and C.col3 = 5;   
  7. Execution Plan   
  8. SELECT STATEMENT Optimizer=RULE   
  9. 0  MERGE JOIN   
  10. 1 SORT (JOIN)   
  11. 2  NESTED LOOPS   
  12. 3 TABLE Access (FULL) OF 'B'   
  13. 3 TABLE Access (BY INDEX ROWID) OF 'A'   
  14. INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
  15. 1 SORT (JOIN)   
  16. 7  TABLE Access (FULL) OF 'C'  
  17. select A.col4   
  18. from B, A, C   
  19. where A.col1 = B.col1   
  20. and A.col2 = C.col2;   
  21. Execution Plan   
  22. SELECT STATEMENT Optimizer=RULE   
  23. 0  MERGE JOIN   
  24. 1 SORT (JOIN)   
  25. 2  NESTED LOOPS   
  26. 3 TABLE Access (FULL) OF 'B'   
  27. 3 TABLE Access (BY INDEX ROWID) OF 'A'   
  28. 5  INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
  29. 1 SORT (JOIN)   
  30. 7  TABLE Access (FULL) OF 'C'  

將A表上的索引inx_col12A刪除後:

  1. select A.col4   
  2. from B, A, C   
  3. where A.col1 = B.col1   
  4. and A.col2 = C.col2;   
  5. Execution Plan   
  6. SELECT STATEMENT Optimizer=RULE   
  7. MERGE JOIN   
  8. 1 SORT (JOIN)   
  9. 2  MERGE JOIN   
  10. 3 SORT (JOIN)   
  11. 4  TABLE Access (FULL) OF 'C'   
  12. 3 SORT (JOIN)   
  13. 6  TABLE Access (FULL) OF 'A'   
  14. 1 SORT (JOIN)   
  15. 8  TABLE Access (FULL) OF 'B' 

通過上面的這些例子,使我對Oracle文檔上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”這句話持懷疑態度。此時,我也不能使用hints來強制優化器使用nested loop,如果使用了hints,這樣就自動使用CBO優化器,而不是RBO優化器了。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved