【SQL 效能最佳化】表的三種連線方式

NESTED LOOP:
對於被連線的資料子集較小的情況，巢狀迴圈連線是個較好的選擇。在巢狀迴圈中，內表被外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大於1 萬不適合），要把返回子集較小表的作為外表（CBO 預設外表是驅動表），而且在內表的連線欄位上一定要有索引。當然也可以用ORDERED 提示來改變CBO預設的驅動表，使用USE_NL(table_name1 table_name2)可是強制CBO 執行巢狀迴圈連線。
HASH JOIN :
雜湊連線是CBO 做大資料集連線時的常用方式，最佳化器使用兩個表中較小的表（或資料來源）利用連線鍵在記憶體中建立雜湊表，然後掃描較大的表並探測雜湊表，找出與雜湊表匹配的行。這種方式適用於較小的表完全可以放於記憶體中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下並不能完全放入記憶體，這時最佳化器會將它分割成若干不同的分割槽，不能放入記憶體的部分就把該分割槽寫入磁碟的臨時段，此時要有較大的臨時段從而儘量
提高I/O 的效能。也可以用USE_HASH(table_name1 table_name2)提示來強制使用雜湊連線。如果使用雜湊連線HASH_AREA_SIZE 初始化引數必須足夠的大，如果是9i，Oracle建議使用SQL工作區自動管理，設定WORKAREA_SIZE_POLICY 為AUTO，然後調整PGA_AGGREGATE_TARGET 即可。

排序合併連線
通常情況下雜湊連線的效果都比排序合併連線要好，然而如果行源已經被排過序，在執行排序合併連線時不需要再排序了，這時排序合併連線的效能會優於雜湊連線。可以使用USE_MERGE(table_name1 table_name2)來強制使用排序合併連線

 

 

 

轉載biti的一段話:   
  舉例，表連線返回一條記錄   
  存在兩個表，一個   10條記錄   ，一個1000萬條記錄   
  若2表都存在連線欄位索引，若以小表為驅動表，則   
  代價：     
  10*   (透過索引在大表查詢一條記錄的代價)     
  若以大表為驅動表：   
  1000萬   *   (透過索引在小表中查詢一條記錄的代價)   
  透過索引獲取一條記錄，10rows的表，代價通常在   3   blocks     
  索引2塊，表一塊   
  而如果是1000萬的表，索引可能達到4塊表一塊   
  這樣一來參考上面的計算，你說哪個更好？很顯然！   
    
  小表查詢參考   
  SQL>   create   table   test   as   select   *   from   all_objects   where   rownum   <   11;   
    
  Table   created.   
    
  SQL>   create   index   test_index   on   test(object_id);   
    
  Index   created.   
    
  SQL>   select   object_id   from   test;   
    
  OBJECT_ID   
  ----------   
  18159   
  7781   
  4841   
  19891   
  22549   
  17099   
  17712   
  4287   
  10107   
  19135   
    
  10   rows   selected.   
    
    
  Execution   Plan   
  ----------------------------------------------------------   
  0   SELECT   STATEMENT   ptimizer=CHOOSE   
  1   0   TABLE   ACCESS   (FULL)   OF   'TEST'   
    
  Statistics   
  ----------------------------------------------------------   
  0   recursive   calls   
  12   db   block   gets   
  6   consistent   gets   
  0   physical   reads   
  0   redo   size   
  736   bytes   sent   via   SQL*Net   to   client   
  425   bytes   received   via   SQL*Net   from   client   
  2   SQL*Net   roundtrips   to/from   client   
  0   sorts   (memory)   
  0   sorts   (disk)   
  10   rows   processed   
    
  SQL>   select   *   from   test   where   object_id   =   4287;   
    
  OWNER   OBJECT_NAME   
  ------------------------------   ------------------------------   
  SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE   
  ------------------------------   ----------   --------------   ------------------   
  CREATED   LAST_DDL_   TIMESTAMP   STATUS   T   G   S   
  ---------   ---------   -------------------   -------   -   -   -   
  SYS   /1033c8a_SqlTypeWithMethods   
  4287   JAVA   CLASS   
  14-NOV-00   03-JUL-03   2003-07-03:11:18:19   INVALID   N   N   N   
    
    
    
  Execution   Plan   
  ----------------------------------------------------------   
  0   SELECT   STATEMENT   ptimizer=CHOOSE   
  1   0   TABLE   ACCESS   (BY   INDEX   ROWID)   OF   'TEST'   
  2   1   INDEX   (RANGE   SCAN)   OF   'TEST_INDEX'   (NON-UNIQUE)   
    
  Statistics   
  ----------------------------------------------------------   
  0   recursive   calls   
  0   db   block   gets   
  3   consistent   gets   
  0   physical   reads   
  0   redo   size   
  1157   bytes   sent   via   SQL*Net   to   client   
  425   bytes   received   via   SQL*Net   from   client   
  2   SQL*Net   roundtrips   to/from   client   
  0   sorts   (memory)   
  0   sorts   (disk)   
  1   rows   processed