【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程序员面试笔试宝典》，作者：李华荣。
