程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 在Oracle優化中所用到的語句有哪些

在Oracle優化中所用到的語句有哪些

編輯：Oracle數據庫基礎

我們今天是要和大家一起討論的是Oracle優化的查詢，我前兩天在相關網站看見的資料，覺得挺好，就拿出來供大家分享。如果你對Oracle優化的查詢，心存好奇的話，以下的文章將會揭開它的神秘面紗。

假設LARGE_TABLE是一個較大的表，且username列上沒有索引，則運行下面的語句：

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

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

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

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

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

SELECT STATEMENT Optimizer=CHOOSE Cost=   
SELECT STATEMENT Optimizer=CHOOSE

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

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

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

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

例2：

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

select A.col4   
from A , B , C   
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5   
Execution Plan   
SELECT STATEMENT Optimizer=CHOOSE   
MERGE JOIN   
SORT (JOIN)   
NESTED LOOPS   
TABLE Access (FULL) OF 'B'   
TABLE Access (BY INDEX ROWID) OF 'A'   
INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)   
SORT (JOIN)   
TABLE Access (FULL) OF 'C'   
Statistics   
0 recursive calls   
8 db block gets   
6 consistent gets   
0 physical reads   
0 redo size   
551 　bytes sent via SQL*Net to clIEnt   
430 　bytes received via SQL*Net from clIEnt   
2 SQL*Net roundtrips to/from clIEnt   
2 sorts (memory)   
0 sorts (disk)   
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)。

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

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

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

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

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

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

Execution Plan  
SELECT STATEMENT Optimizer=CHOOSE   
MERGE JOIN   
SORT (JOIN)   
NESTED LOOPS   
TABLE Access (FULL) OF 'B'   
TABLE Access (BY INDEX ROWID) OF 'A'   
SORT (JOIN)   
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表做排序—合並連接。

上述的相關內容就是對Oracle優化查詢的描述，希望會給你帶來一些幫助在此方面。