四種聯結方法——《Oracle SQL 高階程式設計》筆記1

聯接的方法有四種：巢狀迴圈聯接、雜湊聯接、排序-合併聯接和笛卡爾連線


每個聯接方法都有兩個分支。所訪問的第一章表稱為驅動表（the driving table），訪問的第二張表則稱為內層表或被驅動表（inner 或 driven-to table）。最佳化器透過使用統計資訊和 where 子句中的篩選條件計算每個表分別返回多少行資料來確定那張表是驅動表，預估大小最小（塊、資料行、位元組）的表通常被作為驅動表。


巢狀迴圈聯接
巢狀迴圈聯接使用一次訪問運算所得到的結果集中的每一行來與另一個表進行對碰。如果結果集的大小是有限的，並且在用來聯接的列上建有索引的話，這種聯接的效率通常是最高的。巢狀迴圈聯結運算成本主要是讀取外層表中每一行並將其所匹配的內層表中的行聯結所需的成本。
這種聯接的強大之處在於所使用的記憶體是非常少的。因為資料行集一次只加工一行，所需開支也是非常小的。巢狀迴圈聯接的基本度量就是為了準備最終結果集所需要訪問的資料塊數目。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


這個查詢處理過程如下虛擬碼：
for each row in (select empno, ename, deptno from emp) loop
  for (select dname, loc from dept where deptno = outer.deptno) loop
    if match then pass the row on to the next step
    if inner join and no match then discard the row
    if outer join and no match set inner column values to null and pass the row on to the next step
  end loop;
end loop;




排序-合併聯接
排序-合併聯接獨立地讀取需要聯接的兩張表，對每張表中滿足 where 子句的資料行按照聯結鍵進行排序，然後對排序後的資料行集進行合併。對這種聯接方法來說，排序的開銷是最大的，但是一旦資料行集排序完成了，合併的過程是非常快的。為了進行合併，資料庫輪流操作兩個列表，比較最上面的資料行，丟棄在排序佇列中比另一列表中的最上面一行出現的早的資料行，並只返回匹配的行。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


這個查詢處理過程如下虛擬碼：
select empno, ename, deptno from emp order by deptno
select dname, loc, deptno from dept order by deptno
compare the rowsets and return rows where deptno in both lists match
for an outer join, compare the rowsets and return all rows from the first list 
setting column values for the other table to null


排序-合併聯接一般最適合於資料篩選條件有限並返回有限資料行的查詢。如果沒有可用的更直接訪問資料的索引時，排序-合併聯接也通常是較好的選擇。總而言之，在條件為非等式的時候排序-合併聯接通常是最好的選擇。如果資料行源非常大，排序-合併聯接就可能是唯一可行的選擇。




雜湊聯接（hash join）
雜湊聯接首先應用 where 子句中的篩選標準來獨立地讀取要進行聯結的兩個表。基於表和索引的統計資訊，被確定為返回最少行數的包被完全雜湊化到記憶體中。這個雜湊化了的表包含了原表的所有資料行並被基於將聯結鍵轉化為雜湊值的隨機行數載入到雜湊桶中。只要有足夠的記憶體空間，這個雜湊表將一直放在記憶體中，然而如果沒有足夠的記憶體，雜湊表將會被寫入到臨時磁碟空間。
下一步就是讀取另一個較大的表，並對聯結鍵列應用雜湊函式。然後利用這個雜湊值對較小的在記憶體中的雜湊表進行探測以尋找匹配的第一個表的行資料所在的雜湊桶。每個雜湊桶都有一個放在其中的資料雜湊表（透過點陣圖來表示）。這個列表被用來與探測行進行匹配。如果匹配成功則返回一行資料，否則丟棄。較大的表只讀取一次，並檢查其中的每一行來尋找匹配。這與巢狀迴圈聯結的不同之處在於此處內層表被多次讀取。因此事實上在這個例子中，較大的表是驅動表，因此僅讀取一次，而較小的雜湊表則被探測很多次。與巢狀迴路聯結執行計劃不同，在執行計劃的輸出中較小的雜湊表放在前面而較大的探測表放在後面。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


這個查詢處理過程如下虛擬碼：
determine the smaller row set, or in the case of an outer join, use the outer joined table
select dname, loc, deptno from dept
hash the deptno column and build a hash table
select empno, ename, deptno from emp
hash the deptno column and probe the hash table
if match made, check bitmap to confirm row match 
if no match made, discard the row


決定哪個表是最小的不僅取決於資料行數，還取決於這些行的大小，因為整個行都必須要存放在雜湊表中。當資料行源較大並且結果集也比較大的情況下，將更傾向於考慮雜湊聯接。或者，如果要聯結的其中一張表確定總是返回同一資料行源，也很可能會選用雜湊聯結，因為這樣僅訪問一次這張表。最後，如果較小的表可以放到記憶體中，雜湊聯結也會很受歡迎。
雜湊聯結僅在相等聯結的情況下才能進行。




笛卡爾聯結
笛卡爾聯結髮生在當一張表中的所有行與另一張表中的所有行聯結的時候。因此，這種聯接所得到的結果集總是=rows table A * rows table B。通常當連線條件被忽略或忽視以致沒有指定的聯結列，所能做的唯一可能的運算就是將一張表中的所有內容與另一張表中的所有內容聯結起來的時候會進行笛卡爾聯結。




例：
select empno, ename, loc 
from emp, dept;


這個查詢處理過程如下虛擬碼：
determine the table
select dname, loc, deptno from dept
select empno, ename, deptno from emp
for each row in dept match it to every row in emp retaining all rows