【DB筆試面試589】在Oracle中，什麼是半連接（Semi Join）？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，什麼是半連接（Semi Join）？
♣
答案部分
半連接（Semi Join）是一種特殊的連接類型，當做子查詢展開時，Oracle經常會把那些外部WHERE條件為EXISTS、IN或= ANY的子查詢轉換為對應的半連接。半連接分為嵌套循環半連接（Hint為：NL_SJ）、排序合併半連接（Hint為：MERGE_SJ）和哈希半連接（Hint為：HASH_SJ），不過在新版本資料庫里，都傾向於使用哈希半連接。不過哈希半連接也有一些限制條件，例如，只能使用等值連接、不能使用GROUP BY、CONNECT BY、ROWNUM等限制條件。在執行計劃中若有關鍵字「HASH JOIN SEMI」，則說明Oracle使用了哈希半連接。示例如下所示：
SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ HASH_SJ */ DEPTNO FROM scott.EMP); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 69 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 3 | 69 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ MERGE_SJ */ DEPTNO FROM scott.EMP); ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- SELECT * FROM scott.DEPT A WHERE DEPTNO IN (SELECT /*+ NL_SJ */ DEPTNO FROM scott.EMP); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 69 | 10 (0)| 00:00:01 | | 1 | NESTED LOOPS SEMI | | 3 | 69 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 9 | 27 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
