Oracle連線方式

yuan22003發表於2011-08-23
Oracle
在日常基於資料庫應用的開發過程中，我們經常需要對多個表或者資料來源進行關聯查詢而得出我們需要的結果集。那麼Oracle到底存在著哪幾種連線方式？優化器內部又是怎樣處理這些連線的？哪種連線方式又是適合哪種查詢需求的？只有對這些問題有了清晰的理解後，我們才能針對特定的查詢需求選擇合適的連線方式，開發出健壯的資料庫應用程式。選擇合適的表連線方法對SQL語句執行的效能有著至關重要的影響。下面我們就Oracle常用的一些連線方法及適用情景做一個簡單的介紹。
3.1 巢狀迴圈連線(nested loop)
巢狀迴圈連線的工作方式是這樣的： 
1、 Oracle首先選擇一張表作為連線的驅動表，這張表也稱為外部表（Outer Table）。由驅動表進行驅動連線的表或資料來源稱為內部表（Inner Table）。 
2、 提取驅動表中符合條件的記錄，與被驅動表的連線列進行關聯查詢符合條件的記錄。在這個過程中，Oracle首先提取驅動表中符合條件的第一條記錄，再與內部表的連線列進行關聯查詢相應的記錄行。在關聯查詢的過程中，Oracle會持續提取驅動表中其他符合條件的記錄與內部表關聯查詢。這兩個過程是並行進行的，因此巢狀迴圈連線返回前幾條記錄的速度是非常快的。在這裡需要說明的是，由於Oracle最小的IO單位為單個資料塊，因此在這個過程中Oracle會首先提取驅動表中符合條件的單個資料塊中的所有行，再與內部表進行關聯連線查詢的，然後提取下一個資料塊中的記錄持續地迴圈連線下去。當然，如果單行記錄跨越多個資料塊的話，就是一次單條記錄進行關聯查詢的。 
3、 巢狀迴圈連線的過程如下所示：
Nested loop
  Outer loop
    Inner loop
    我們可以看出這裡面存在著兩個迴圈，一個是外部迴圈，提取驅動表中符合條件的每條記錄。另外一個是內部迴圈，根據外迴圈中提取的每條記錄對內部表進行連線查詢相應的記錄。由於這兩個迴圈是巢狀進行的，故此種連線方法稱為巢狀迴圈連線。
巢狀迴圈連線適用於查詢的選擇性強、約束性高並且僅返回小部分記錄的結果集。通常要求驅動表的記錄（符合條件的記錄，通常通過高效的索引訪問）較少，且被驅動表連線列有唯一索引或者選擇性強的非唯一索引時，巢狀迴圈連線的效率是比較高的。
巢狀迴圈連線驅動表的選擇也是連線中需要著重注意的一點，有一個常見的誤區是驅動表要選擇小表，其實這是不對的。假如有兩張表A、B關聯查詢，A表有1000000條記錄，B表有10000條記錄，但是A表過濾出來的記錄只有10條，這時候顯然用A表當做驅動表是比較合適的。因此驅動表是由過濾條件限制返回記錄最少的那張表，而不是根據表的大小來選擇的。 
    在外連線查詢中，如果走巢狀迴圈連線的話，那麼驅動表必然是沒有符合條件關聯的那張表，也就是後面不加(+)的那張表。這是由於外連線需要提取可能另一張表沒符合條件的記錄，因此驅動表需要是那張我們要返回所有符合條件記錄的表。比如下面這個查詢，
巢狀迴圈連線返回前幾行的記錄是非常快的，這是因為使用了巢狀迴圈後，不需要等到全部迴圈結束再返回結果集，而是不斷地將查詢出來的結果集返回。在這種情況下，終端使用者將會快速地得到返回的首批記錄，且同時等待Oracle內部處理其他記錄並返回。如果查詢的驅動表的記錄數非常多，或者被驅動表的連線列上無索引或索引不是高度可選的情況，巢狀迴圈連線的效率是非常低的
-- 刪除原表
drop table t1;

-- 建立測試表
create table t1(
f1 varchar2(10), 
f2 varchar2(1000)
)
tablespace CTL
  pctfree 98;
  
-- 填充測試內容
insert into t1(f1,f2)
select rownum, lpad(rownum,700,'0') 
from dba_tables a, dba_tab_cols b
where a.owner = b.owner
and rownum < 10000;
commit;

-- 檢查測試內容格式
select sys.dbms_rowid.rowid_block_number(rowid), f1, f2 from t1;
-- 每條記錄都儲存在單獨的資料塊中
select count( distinct sys.dbms_rowid.rowid_block_number(rowid)) from t1;

/*
用同樣的方式建立表t2
*/

-- 刪除原表
drop table t2;

-- 建立測試表
create table t2(
f1 varchar2(10), 
f2 varchar2(1000)
)
tablespace CTL
  pctfree 98;
  
-- 填充測試內容
insert into t2(f1,f2)
select rownum * 10, lpad(rownum * 10,700,'0') 
from dba_tables a, dba_tab_cols b
where a.owner = b.owner
and rownum < 1000;
commit;

-- 檢查測試內容格式
select sys.dbms_rowid.rowid_block_number(rowid), f1, f2 from t2;
-- 每條記錄都儲存在單獨的資料塊中
select count( distinct sys.dbms_rowid.rowid_block_number(rowid)) from t2;

create index ind_t1_f1 on t1(f1);
create index ind_t2_f1 on t2(f1);

--首先我們來看使用nested loop關聯方式, 不同表作為驅動時的情況.
1, 表t2作為驅動表
select /*+ ordered use_nl(t1 , t2)*/
t1.f1, t2.f1
from ctl.t2 t2,ctl.t1 t1
where t1.f1 = t2.f1
  and t1.f1 < 1000;

Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=4 Bytes=56)
   1 0   NESTED LOOPS (Cost=84 Card=4 Bytes=56)
   2 1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=574)
   3 1     INDEX (RANGE SCAN) OF 'IND_T1_F1' (NON-UNIQUE) (Cost=1 C
   ard=1 Bytes=7)
Cost = outer access cost + (inner access cost * outer cardinality)
    Cost = 2 + 1 * 82 = 84;
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
       2020  consistent gets
  23  physical reads
   0  redo size
       2650  bytes sent via SQL*Net to client
 721  bytes received via SQL*Net from client
   8  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  99  rows processed


2, t1作為驅動表
Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=56)
   1 0   NESTED LOOPS (Cost=6 Card=4 Bytes=56)
   2 1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=4 Bytes=28)
   3 1     INDEX (RANGE SCAN) OF 'IND_T2_F1' (NON-UNIQUE) (Cost=1 C
   ard=1 Bytes=7)
Cost = outer access cost + (inner access cost * outer cardinality)
    Cost = 2 + 1 * 4 = 84;
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
      11123  consistent gets
   3  physical reads
   0  redo size
       2650  bytes sent via SQL*Net to client
 721  bytes received via SQL*Net from client
   8  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  99  rows processed
3.2, 雜湊連線(hash join)
雜湊連線分為兩個階段，如下。 
1、 構建階段：優化器首先選擇一張小表做為驅動表，運用雜湊函式對連線列進行計算產生一張雜湊表。通常這個步驟是在記憶體（hash_area_size）裡面進行的，因此運算很快。 
2、 探測階段：優化器對被驅動表的連線列運用同樣的雜湊函式計算得到的結果與前面形成的雜湊表進行探測返回符合條件的記錄。這個階段中如果被驅動表的連線列的值沒有與驅動表連線列的值相等的話，那麼這些記錄將會被丟棄而不進行探測
雜湊連線比較適用於返回大資料量結果集的連線。
使用雜湊連線必須是在CBO模式下，引數hash_join_enabled設定為true，
雜湊連線只適用於等值連線。從Oracle9i開始，雜湊連線由於其良好的效能漸漸取代了原來的排序合併連線。

SQL> select /*+ ordered use_hash(t1 , t2)  */
t1.f1, t2.f1
from ctl.t1 t1,ctl.t2 t2
where t1.f1 = t2.f1  2    3    4  ;

999 rows selected.


Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=1148
   )
   1 0   HASH JOIN (Cost=5 Card=82 Bytes=1148)
   2 1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=574)
   3 1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=574)

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
      11113  consistent gets
   0  physical reads
   0  redo size
      23590  bytes sent via SQL*Net to client
       1381  bytes received via SQL*Net from client
  68  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
 999  rows processed
3.3, 排序合併連線(merge join)
排序合併連線的方法非常簡單。在排序合併連線中是沒有驅動表的概念的，兩個互相連線的表按連線列的值先排序，排序完後形成的結果集再互相進行合併連線提取符合條件的記錄。相比巢狀迴圈連線，排序合併連線比較適用於返回大資料量的結果。  
排序合併連線在資料表預先排序好的情況下效率是非常高的，也比較適用於非等值連線的情況，比如>、>=、<=等情況下的連線（雜湊連線只適用於等值連線）。由於Oracle中排序操作的開銷是非常消耗資源的，當結果集很大時排序合併連線的效能很差，於是Oracle在7.3之後推出了新的連線方式——雜湊連線。
1, rbo模式;
2, 不等於關聯(> < >= <= <>)
3, hash_join_enabled = false;
4, 資料來源已排序

相關文章