Oracle最佳化器介紹

Oracle最佳化器介紹

本文講述了Oracle最佳化器的概念、工作原理和使用方法，兼顧了Oracle8i、9i以及最新的10g三個版本。理解本文將有助於您更好的更有效進行SQL最佳化工作。

RBO最佳化器

RBO是一種基於規則的最佳化器，隨著CBO最佳化器的逐步發展和完善，在最新的10g版本中Oracle已經徹底廢除了RBO。正在使用Oracle8i或9i的人們或多或少的都會碰到RBO，因此在詳細介紹CBO之前，我們有必要簡單回顧一下古老的RBO最佳化器。

在RBO中Oracle根據可用的訪問路徑和訪問路徑的等級來選擇執行計畫，等級越高的訪問路徑通常運行SQL越慢，如果一個語句有多個路徑可走，Oracle總是選擇等級較低的訪問路徑。

RBO訪問路徑

1級：用Rowid定位單行

當WHERE子句中直接嵌入Rowid時，RBO走此路徑。Oracle不推薦直接引用Rowid，Rowid可能會由於版本的改變而變化，行遷移、行連結、EXP/IMP也會使Rowid發生變化。

2級：用Cluster Join定位單行

兩個表做等值串連，一方的串連欄位是Cluster Key，且WHERE中存在可以保證該語句僅返回一行記錄的條件時，RBO走此路徑。

3級：用帶用唯一約束或做主鍵的Hash Cluster Key定位單行

4級：用唯一約束的欄位或做主鍵的欄位來定位單行

5級：Cluster Join

6級：使用Hash Cluster Key

7級：使用索引Cluster Key

8級：使用複合索引

9級：使用單欄位索引

10級：用索引進行有界限範圍的尋找

如，column >[=] expr AND column <[=] expr或column BETWEEN expr AND expr

或column LIKE ‘c%’

11級：用索引欄位進行無界限的尋找

如，WHERE column >[=] expr 或 WHERE column <[=] expr

12級：排序合并串連

13級：對索引欄位使用MAX或MIN函數

14級：ORDER BY索引欄位

15級：全表掃描

如果可以使用索引RBO會儘可能的去用索引而不是全表掃描，但是在下列一些情況RBO只能使用全表掃描：

如果column1和column2是同一個表的欄位，含有條件column1 < column2或column1 > column2或column1 <= column2或column1 >= column2，RBO會用全表掃描。

如果使用column IS NULL或column IS NOT NULL或column NOT IN或column != expr或column LIKE ‘%ABC’時，不論column有無索引，RBO都使用全表掃描。

如果expr = expr2，expr運算式作用了一個欄位上，無論該欄位有無索引，RBO都會全表掃描。

NOT EXISTS子查詢以及在視圖中使用ROWNUM也會造成RBO進行全表掃描。

以上就是RBO的全部可用訪問路徑。RBO最佳化器死板的根據規則來選擇執行計畫顯然不夠靈活，在RBO中也無法使用物化視圖等Oracle提供的新特性，在Oracle8i時CBO已經基本成熟，因此Oracle強烈建議改用CBO最佳化器。下文將全面介紹CBO最佳化器。

CBO最佳化器結構

CBO是基於成本的最佳化器，它根據可用的訪問路徑、對象的統計資訊、嵌入的Hints來選擇一個成本最低的執行計畫。

CBO主要包含以下組件：

• l 查詢轉換器（Query Transformer）
• l 評估器（Estimator）
• l 計劃產生器（Plan Generator）

如所示：

查詢轉換器

查詢語句的形式會影響所產生的執行計畫，查詢轉換器的作用就是改變查詢語句的形式以產生較好的執行計畫。

從Oracle 8i開始就有四種轉換技術：視圖合并（View Merging）、謂詞推進（Predicate Pushing）、非嵌套子查詢（Subquery Unnesting）和物化視圖的查詢重寫（Query Rewrite with Materialized Views）。

視圖合并：如果SQL語句中含有視圖，經分析後會把視圖放在獨立的“視圖查詢塊”中，每個視圖會產生一個視圖子計劃，當為整個語句產生執行計畫時，視圖子計劃會被直接拿來使用而不會照顧到語句的整體性，這樣就很容易導致不良執行計畫的產生。視圖合并就是為了去掉“視圖查詢塊”，將視圖合并到一個整體的查詢塊中，這樣就不會有視圖子計劃產生，執行計畫的優良性得到提升。

謂詞推進：不是所有的視圖都能夠被合并，對於那些不能被合并的視圖Oracle會將相應的謂詞推進到視圖查詢塊中，這些謂詞通常是可索引的或者是過濾性較強的。

非嵌套子查詢：子查詢和視圖一樣也是被放於獨立查詢塊中的，查詢轉換器會將絕大多數子查詢轉換為串連從而合并為同一查詢塊，少量不能被轉換為串連的子查詢，會將它們的子計劃安照一個高效的方式排列。

物化視圖的查詢重寫：當query_rewrite_enabled=true時，查詢轉換器尋找與該查詢語句相關聯的物化視圖，並用物化視圖改寫該查詢語句。

關於“窺視”（Peeking）:

在Oracle9i中為查詢轉換器增加了一個功能，就是當使用者使用綁定變數時，查詢轉換器可以“偷窺”綁定變數的實際值。

我們知道使用綁定變數雖然可以有效減少“硬分析”，但它帶來的負面影響是最佳化器無法根據實際的資料分布來最佳化SQL，很有可能本可以走索引的SQL卻做了全表掃描。“窺視”正是為瞭解決這個問題，但是它並沒有徹底的解決，Oracle只允許第一次調用時進行“窺視”，接下來的調用即使綁定變數的值發生了變化，也仍然是使用第一次產生的執行計畫，這就造成了一個錯誤的執行計畫會被多次使用，10g中的“窺視”也是如此。

評估器

評估器通過計算三個值來評估計劃的總體成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）。

選擇性：是一個大於0小於1的數，0表示沒有記錄被選定，1表示所有記錄都被選定。統計資訊和長條圖關係到選擇性值的準確性。如：name=’Davis’，如果不存在統計資訊評估器將根據所用的謂詞來指定一個預設的選擇性值，此時評估器會始終認為等式謂詞的選擇性比不等式謂詞小；如果存在統計資訊而不存在長條圖，此時選擇性值為1/count(distinct name)；如果存在統計資訊也存在長條圖，選擇性值則為count（name）where name=’Davis’ / count（name）where name is not null。

基數：通常表中的行數稱為“基礎基數”（Base cardinality）；當用WHERE中的條件過濾後剩下的行數稱為“有效基數”（Effective cardinality）；串連操作之後產生的結果集行數稱為“串連基數”（Join cardinality）；一個欄位DISTINCT之後的行數稱為“DISTINCT基數”；“GROUP基數”（Group cardinality）比較特殊，它與基礎基數和DISTINCT基數有關，例如：group by colx則GROUP基數就等於基礎基數，但是group by colx,coly的GROUP基數則大於max ( distinct cardinality of colx , distinct cardinality of coly )且小於min ( (distinct cardinality of colx * distinct cardinality of coly) , base cardinality)。

成本：就是度量資源消耗的單位。可以理解為執行表掃描、索引掃描、串連、排序等操作所消耗I/O、CPU、記憶體的數量。

計劃產生器

計劃產生器的作用就是產生大量的執行計畫，然後選擇其中總體成本最低的一個。

由於不同的訪問路徑、串連方式和串連順序可以任意組合，雖然以不同的方式訪問和處理資料，但是可以產生同樣的結果，因此一個SQL可能存在大量不同的執行計畫。但實際上計劃產生器很少會實驗所有的可能存在的執行計畫，如果它發現當前執行計畫的成本已經很低了，它將停止實驗，相反當前計劃的成本如果很高，它將繼續實驗其他執行計畫，因此如果能使計劃產生器一開始就找到成本很低的執行計畫，則會大量減少所消耗的時間，這也正是我們為什麼用HINTS來最佳化SQL的原因之一。

最佳化器模式及最佳化目標

除了上述的CBO最佳化器外，Oracle還有一種基於規則的RBO最佳化器，在8i以後Oracle就不再發展RBO了，有很多新特性在RBO中也不被支援，在最新的10g中RBO已被徹底廢除。在10g前RBO與CBO共存，使用者可以通過設定初始化參數OPTIMIZER_MODE來決定到底使用哪個最佳化器，也可以用ALTER SESSION來改變當前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入HINTS可以指定具體某個SQL使用哪個最佳化器。

CBO雖然是基於成本的最佳化器，但仍然允許以“時間”或者說“響應速度”為最佳化目標，通過設定OPTIMIZER_MODE或者對具體語句嵌入HINTS都可以指定最佳化目標。

OPTIMIZER_MODE選項如下：

• l ALL_ROWS
• l FIRST_ROWS_n
• l FIRST_ROWS
• l CHOOSE
• l RULE

CHOOSE

僅在9i及之前版本中被支援，10g已經廢除。8i及9i中為預設值。

這個值表示SQL語句既可以使用RBO最佳化器也可以使用CBO最佳化器，而決定該SQL到底使用哪個最佳化器的唯一因素是，所訪問的對象是否存在統計資訊。如果所訪問的全部對象都存在統計資訊，則使用CBO最佳化器最佳化SQL；如果只有部分對象存在統計資訊，也仍然使用CBO最佳化器最佳化SQL，最佳化器會為不存在統計資訊對象依據一些內在資訊（如分配給該對象的資料區塊）來產生統計資訊，只是這樣產生的統計資訊可能不準確，而導致產生不理想的執行計畫；如果全部對象都無統計資訊，則使用RBO來最佳化該SQL語句。

RULE

僅在9i及之前版本中被支援，10g已經廢除。

不論是否存在統計資訊，都將使用RBO最佳化器來最佳化SQL。

ALL_ROWS

在10g中為預設值。

不論是否存在統計資訊，都使用CBO最佳化器，且把CBO的最佳化目標設定為“最小的成本”。

FIRST_ROWS

CBO儘可能快速的返回結果集的前面少數行記錄。

不論是否存在統計資訊，都使用CBO最佳化器，FIRST_ROWS導致CBO使用“試探法”來產生執行計畫，這種方式其成本可能會稍大一些。

FIRST_ROWS_n

不論是否存在統計資訊，都使用CBO最佳化器，並以最快的速度返回前n行記錄，n可以是1,10,100,1000。

影響最佳化器模式及目標的HINTS：

• l RULE：意義同OPTIMIZER_MODE=RULE區別在於HINTS作用在語句級，10g中該HINTS已被廢棄。
• l CHOOSE：意義同OPTIMIZER_MODE=CHOOSE，10g中已被廢棄。
• l FIRST_ROWS：意義同OPTIMIZER_MODE=FIRST_ROWS，10g中已被廢棄。
• l ALL_ROWS：意義同OPTIMIZER_MODE=ALL_ROWS。
• l FIRST_ROWS(n)：意義同OPTIMIZER_MODE=FIRST_ROWS_n。
• l CPU_COSTING：啟用CPU成本計算，也就是在總成本中考慮CPU的成本，預設是啟用的。該HINTS是10g中新增加的。
• l NO_CPU_COSTING：關閉CPU成本計算，也就是在總成本中不考慮CPU的成本，只計算I/O的成本。該HINTS也是10g中新增加的。

訪問路徑

訪問路徑就是從資料庫裡檢索資料的方式。最佳化器首先檢查WHERE子句和FROM子句的條件，確定有哪些訪問路徑是可用的。然後最佳化器使用這些訪問路徑或各訪問路徑的聯合，產生一組可能存在的執行計畫，再通過索引、欄位、表的統計資訊評估每個計劃的成本，最後最佳化器選擇成本最低的執行計畫所對應的訪問路徑。

如果SQL語句的FROM子句無SAMPLE或SAMPLE BLOCK，最佳化器在選擇訪問路徑的時候會優先考慮語句中的HINTS。

最佳化器可用的訪問路徑如下：

• l 全表掃描（Full Table Scans）
• l Rowid掃描（Rowid Scans）
• l 索引掃描（Index Scans）
• l 簇掃描（Cluster Scans）
• l 散列掃描（Hash Scans）
• l 表取樣掃描（Sample Table Scans）

全表掃描

全表掃描將讀取HWM之下的所有資料區塊，所有行都要經WHERE子句過濾看是否滿足條件。當Oracle執行全表掃描時會按順序讀取每個塊且唯讀一次，如果能夠一次讀取多個塊，可以有效提高效率，初始化參數DB_FILE_MULTIBLOCK_READ_COUNT用來設定在一次I/O中可以讀取多少個資料區塊。 通常我們認為應該避免全表掃描，但是在檢索大量資料時全表掃描優於索引掃描，這正是因為全表掃描可以在一次I/O中讀卻多個塊，從而減少了I/O的次數。在使用全表掃描的同時也可以使用並行來提高掃描的速度。

CBO最佳化器何時會選擇全表掃描：

1） 無合適的索引。

2） 檢索表中絕大多數的資料。

3） 表非常小。比如，表中的塊小於DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果這樣的表被頻繁使用應該alter table table_name storage(buffer_pool keep)。

4） 高並行度。如果在表級設定了較高的並行度，如alter table table_name parallel(degree 10)，通常會使CBO選擇全表掃描。通常建議在語句級用HINTS來實現並行，如/*+full(table_name) parallel(table_name degree)*/。

5） 太舊的統計資料。如果表沒有進行過分析或很久沒有再次分析，CBO可能會錯誤的認為表含有及少的資料區塊。

6） 在語句中嵌入了全表掃描的HINTS。

Rowid掃描

Rowid表示行在資料區塊中的具體位置，Rowid是尋找具體行的最快方式。可以在WHERE子句中寫入Rowid，但是不推薦這麼做。通常都是通過索引來獲得Rowid，但如果被檢索的行都包含在索引中時，直接存取索引就能得到所需的資料則不會使用Rowid。

索引掃描

索引不僅包含被索引的欄位值，還包含行的位置標識Rowid，如果語句只檢索索引欄位，Oracle將直接從索引中讀取而不需要通過Rowid去訪問表，如果語句通過索引檢索其他欄位值，則Oracle通過索引獲得Rowid從而迅速找到具體的行。

索引掃描類型：

1） 唯一索引掃描（Index Unique Scans）

2） 索引範圍掃描（Index Range Scans）

3） 索引降序範圍掃描（Index Range Scans Descending）

4） 跳躍式索引掃描（Index Skip Scans）

5） 全索引掃描（Full Index Scans）

6） 快速全索引掃描（Fast Full Index Scans）

7） 索引串連（Index Joins）

在解釋上述索引掃描類型之前，首先要明確一個問題——Oracle對I/O的評估是針對“塊”的而不是“行”。最佳化器在決定是使用全表掃描還是索引掃描時，看的是所涉及塊佔全表的比例而不是檢索的行占表的比例。當然如果一個塊中只包含一個行資料，那麼訪問塊和行是等同的，但是通常情況下都是一個塊中含有多個行的資料，因此如果檢索的行都聚集在少數塊中則會大大降低I/O。

例如：一個有9行資料的表佔據三個資料區塊，在STATUS欄位上有一個非唯一索引，該欄位共有三類不同的值分別是1、2、3。

第一種情況：

                 Block 1       Block 2        Block 3 

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

                 1  1  1       2  2  2        3  3  3   

索引欄位（STATUS）相同的值都聚集在表的同一個物理塊中，這種情況下擷取STATUS=1的資料只需讀取表的一個物理塊，即一次I/O。

第二種情況：

                 Block 1       Block 2        Block 3 

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

                 1  2  3       1  2  3        1  2  3

索引欄位（STATUS）相同的值被分散在表中三個物理塊，這時要得到STATUS=1的資料則要讀取表的三個物理塊，即三次I/O才能獲得。

1）唯一索引掃描

在利用一個主鍵欄位或含有唯一約束的欄位選擇一行記錄時，通常發生唯一索引掃描。

2）索引範圍掃描

索引範圍掃描返回的資料返照索引欄位值升序排列，值相同的按Rowid升序排列。如果在語句中使用了ORDER BY ASC子句，而且排序欄位是索引欄位時Oracle不會對ORDER BY再次排序。

例如：

SQL> select * from t;

       COLX              COLY

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

         1                   3

         1                   2

         1                   1

         1                   0

SQL> create index ind_t on t(coly);

SQL> set autotrace on

SQL> select * from t where coly>0;

      COLX                COLY

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

         1                    1

         1                    2

         1                    3

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

沒有使用ORDER BY結果集已經是按COLY升序排列。

SQL> set autotrace traceonly

SQL> select * from t where coly>0 order by coly;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

可以看到執行計畫中無SORT 步驟，說明Oracle忽略了ORDER BY子句。

最佳化器在下列情況會使用索引範圍掃描：

→ COL1 = :b1

→ COL1 > :b1

→ COL1 < :b1

→ COL1 LIKE ‘ABC%’會做索引範圍掃描，而COL1 LIKE ‘%ABC’則不會。

→ 對於複合索引，通常只有複合索引的第一個欄位包含在AND條件之中時才會使用複合索引。

3）索引降序範圍掃描

如果在order by中指定了索引是降序排列的，或者使用了INDEX_DESC提示，最佳化器會使用索引降序範圍掃描。

例如：

SQL> select /*+index_desc(t ind_t)*/colx,coly from t where coly<3;

     COLX                COLY

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

       1                      2

       1                      1

       1                      0

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104)

2 1 INDEX (RANGE SCAN DESCENDING) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1)

4）跳躍式索引掃描

跳躍式索引掃描是用來提高複合索引效率的，通常當複合索引的第一個索引欄位不在語句中指定時是無法使用複合索引的，此時如果複合索引的第一個索引欄位DISTINCT值非常小，而複合索引的其他索引欄位DISTINCT值非常大時，可以使用跳躍式索引掃描來跳過該複合索引的第一個索引欄位。跳躍式掃描會使複合索引在邏輯上分裂成N個較小的索引，N值等於複合索引的第一個索引欄位的DISTINCT值。

例如：

SQL> select* from employees;

  SEX         EMPLOYEE_ID           ADDRESS

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

   F               98                         ABC

   F               100                       ABC

   F               102                       ABC

   F               104                       ABC

   M              101                       ABC

   M              103                       ABC

   M              105                       ABC

SQL> create index ind_sex_empid on employees(sex,employee_id);

SQL>set autotrace traceonly

SQL>select/*+index_ss(employees ind_sex_empid)*/* from employees where employee_id=101;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=3 Card=1 Bytes=11)

2 1 INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE) (Cost=2 Card=1)

索引IND_SEX_EMPID結構如下：

5）全索引掃描

當查詢涉及的欄位都包含在索引中，如果WHERE子句中謂詞非第一個索引欄位，或無WHERE子句但是被索引欄位中至少有一個非空屬性時，通常會做全索引掃描。全索引掃描結果集按索引欄位排序。

6）快速全索引掃描

當查詢涉及的欄位都包含在索引中，且被索引欄位中至少有一個非空屬性時，可以使用INDEX_FFS(table_name index_name)來使語句做快速全索引掃描。快速全索引掃描不同於全索引掃描，它使用多塊讀取的方式來讀全部索引塊，而且可以使用並行讀取。快速全索引掃描的結果集不會排序。位元影像索引不能使用快速全索引掃描。

7）索引串連

索引串連是幾個索引的散列串連。如果查詢的欄位上都存在索引，可以使用索引串連來避免訪問表。

例如：

SQL> select* from t;

        COL1               COL2              COL3              COL4

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

         1                     2                    3                   4

         1                     1                    3                   4

         1                     1                    5                   4

SQL> create index ind_col1 on t(col1);

索引已建立。

SQL> create index ind_col2 on t(col2);

索引已建立。

SQL> create index ind_col3 on t(col3);

索引已建立。

不使用索引串連：

SQL> set autotrace traceonly

SQL> select col1,col2,col3 from t where col2>0;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND_COL2' (NON-UNIQUE)

使用索引串連：

SQL> select /*+index_join(t ind_col1 ind_col2 ind_col3)*/col1,col2,col3 from t where col2>0;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=4 Bytes=156)

1 0 VIEW OF 'index$_join$_001' (Cost=70 Card=4 Bytes=156)

2 1 HASH JOIN

3 2 HASH JOIN

4 3 INDEX (RANGE SCAN) OF 'IND_COL2' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

5 3 INDEX (FAST FULL SCAN) OF 'IND_COL1' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

6 2 INDEX (FAST FULL SCAN) OF 'IND_COL3' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

簇掃描

在被索引的簇中，有著相同簇索引值的行儲存在同一資料區塊中。執行簇掃描時，首先通過掃描簇索引獲得被檢索行的Rowid，然後使用Rowid來定位具體的行。

散列掃描

散列掃描就是在一個散列簇中定位元據行。在一個散列簇中，具有相同散列值的行儲存在相同的資料區塊中。在執行散列掃描時，首先通過一個散列函數來獲得散列值，然後用散列值在資料區塊中定位具體行。

表取樣掃描

當FROM子句後帶有SAMPLE或SAMPLE BLOCK時，會執行表取樣掃描來隨機檢索表中的資料。如：select* from t sample block (1);

處理串連

影響一個串連語句執行計畫的四個重要因素是：訪問路徑、串連方式、串連順序和成本評估。

最佳化器如何處理串連順序

最佳化器首先確定串連的表中是否包含其結果只有一行記錄的表，如果存在這樣的表，最佳化器在對串連表排序時會把這樣的表放在最前端。如果是個外串連，含有（+）操作符的表一定排在不含（+）的表的後面。同理被轉換成ANTI-JOIN或SEMI-JOIN的子查詢，子查詢的表一定排在外部表格的後面，但是HASH-ANTI-JOIN和HASH-SEMI-JOIN在一定情況下可以違反此順序。通常可以用ORDERED來指定串連順序，但是ORDERED所指定的順序如果違反了外串連的順序，則ORDERED將被忽略。

最佳化器如何評估成本

嵌套迴圈串連，在於外表返回的每一行都要在內表中進行匹配的成本，成本計算如下：

cost of nested loop = access cost of outer + (number of rows from outer * access cost of inner)

排序合并串連，在於把兩個大表讀入記憶體並進行排序的成本，成本計算如下：

cost of merge join = access cost of A + access cost of B + (sort cost of A + sort cost of B)

散列串連，在於將小表讀入記憶體分成若干散列表，然後由大表對每個散列表都進行一次匹配的成本，成本計算如下：

cost of hash join = access cost of smaller + (access cost of bigger * number of hash partitions of smaller)

以上成本計算公式不是絕對的，最佳化器對成本的評估還會受到其他因素的影響，比如：記憶體排序區過小會增加排序合并串連的成本，由於此種情況下的排序消耗了過多的CPU和I/O。多塊讀取會降低排序合并串連的成本，如果內表的串連欄位存在索引也會降低嵌套迴圈串連的成本。

最佳化器可用的串連方式

1） 嵌套迴圈串連：適用於外表有效基數較小，內表串連欄位含有索引，且查詢整體返回結果集不太大（小於1萬行）的情況下。HINTS：use_nl

2） 散列串連：適用於查詢整體返回大量結果集，且有較小的串連表可以放入記憶體作為散列表的情況下。適用散列串連要注意HASH_AREA_SIZE要足夠大，可以容下散列表。如果散列表無法完全放入記憶體，要設定較大的臨時段，從而盡量提高I/O效能。HINTS：use_hash

3） 排序合并串連：適用於查詢整體返回大量結果集，兩個大表做串連，且表已經排過序的情況下。當兩個表已經排過序時，使用排序合并串連的效能可能會優於散列串連。HASH_AREA_SIZE和SORT_AREA_SIZE設定過小，可能會導致最佳化器避開散列串連而選擇排序合并串連。HINTS：use_merge

4） 迪卡爾積串連：當兩個表沒有任何串連條件時會使用此串連方式。

一些影響最佳化器的初始化參數

OPTIMIZER_FEATURES_ENABLE：每個版本的Oracle最佳化器特性都不相同，特別是做了版本升級以後一定要修改這個參數才可以使用僅被該版本支援的最佳化器特性。可以賦予它的值如：9.2.0、9.0.2、9.0.1、8.1.7、8.1.6等。

CURSOR_SHARING：這個參數會將SQL語句中的直接量用變數來替換，存在大批直接量的OLTP系統可以考慮啟用這個參數。但是要注意，綁定變數雖然可以使大量的SQL重用，減少分析時間，但是執行計畫可能會不理想。通常OLTP系統適用於綁定變數，OLTP系統特點是，SQL運行頻繁且時間相對較短，SQL的分析時間比重較大。如果在DSS系統中，SQL已耗用時間長，相比之下分析時間微不足道，好的執行計畫才是最重要的，因此DSS系統不建議使用這個參數。

HASH_AREA_SIZE：這是散列表的存放地區，如果使用散列串連這個參數值不能太小，否則對散列串連效能影響很大。如果是9i建議啟動工作區自動管理，然後設定PGA_AGGREGATE_TARGET。

SORT_AREA_SIZE：記憶體排序區的大小，如果排序時記憶體區不夠會寫入磁碟。9i同樣建議啟動工作區自動管理，然後設定PGA_AGGREGATE_TARGET。

HASH_JOIN_ENABLED：只有啟用這個參數，CBO在考慮串連方式的時候才會考慮散列串連。

OPTIMIZER_INDEX_CACHING：這個參數表示被緩衝的索引塊所佔的百分比，可選值的範圍是0-100。這個值會影響嵌套迴圈串連，如果這個值設得較高，CBO將更傾向使用嵌套迴圈。

OPTIMIZER_INDEX_COST_ADJ：最佳化器利用這個參數（是個百分比）把索引掃描的成本轉換為等價的全表掃描的成本，然後與全表掃描的成本進行比較。預設值100，表示索引掃描成本與全表掃描成本等價。可選值範圍是0-10000。

OPTIMIZER_MAX_PERMUTATIONS：這個初始參數用來設定最佳化器最多考慮多少種串連順序，最佳化器不斷的產生可能的表的串連的排列，直到排列數達到參數optimizer_max_permutations為止。一旦最佳化器停止產生新的排列，它將會從中選擇出成本最小的排列。

DB_FILE_MULTIBLOCK_READ_COUNT：這個參數表示在全表掃描或索引快速全掃描時一次I/O讀的連續資料區塊數量（block#連續，且一次I/O不能超過extent）。

OPTIMIZER_MODE：最佳化器模式。值為：RULE、CHOOSE、ALL_ROWS、FIRST_ROWS_n、FIRST_ROWS。

PARTITION_VIEW_ENABLED：如果設定為TRUE, 該最佳化器將跳過分區視圖中未被請求的分區，該參數還能更改基於成本的最佳化程式從基礎資料表統計資訊計算分區視圖統計資訊的方式。

QUERY_REWRITE_ENABLE：如果設定為TRUE，最佳化器將利用可用的物化視圖來重寫SQL。