11. SQL Join Method

SQL join 的方式可分為 sort merge join 、 hash join 與 nested loop join 三大類 :

• Sort Merge join :

當兩個 Table A 與 B 做 sort merge join 時，會以下列步驟進行 :

1. 對 Table A 與 Table B 進行 full scan

2. 將選出的 row 進行排序

3. 將排序過後的 row 進行一對一的 mapping 然後輸出 join 結果

由於排序的動作， sort merge join 往往是一個較沒有效率的方式。當SQL 滿足下列條件時會使用 sort merge join :

． 使用不等式條件進行 join ，例如 a.id  >  b.id

． 當 join 的資料需要排序 (如 distinct 、 order by …等語法)

． 當 join 欄位為 primary key 時

例如 :

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno        order by e.deptno;

由於使用了 order by 語法，因此優化器此時會選擇 sort merge join :

• Hash join :

當兩個 Table A 與 B 進行 Hash Join 時，會以下列步驟進行 :

1. 於 pga 中建立 hash table

2. 對 A 或 B 其中一個 Table 進行 Full Scan (本例為 Table A ) 並將結果放置於 Hash Table 中

3. 對 Table B 進行 Full Scan 並與 hash table 中的資料進行mapping 然後輸出 join 結果

Hash Join 在效能上往往取決於 PGA 中的 Hash Table 是否有足夠的空間可以存放 Table A 的資料，若 PGA 不足則會使用 temporary tablespace 進行 disk read 。當 SQL 滿足下列條件將使用Hash join :

． 當 Table 的資料量較大時且使用 "=" 條件進行join

． 當資料少的 Table 與資料多的 Table 使用 "=" 進行 join 時

例如上述的 SQL ，把 order by 的語法刪除:

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

在有大 Table 的情況下進行 join ，此時將使用 Hash join :

• Nested Loop :

當兩 Table 進行 nested-loop join 時，以下列步驟進行 :

1. 選出一 Table 當做驅動表 (在此稱為 outer table ) ，其他則為inner table

2. 由 where 條件將 outer table 的 row 擷取出來，並逐筆對 inner table 進行 mapping 然後輸出 join 結果

當 outer table 所擷取的資料量越少時， nested-loop 的效能越好。當 SQL 滿足下列條件將使用 Nested Loop :

． 當選取的資料量較少時

． 當優化器使用 Rule-Based 時

同樣以上述 SQL 增加 e.empno=100 條件以減少查詢的資料量 :

SQL> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno    and e.empno=100;

由於查詢的資料量減少，此時優化器將選擇 Nested Loop :