【DB笔试面试589】在Oracle中，什么是半连接（Semi Join）？

AiDBA宝典

发布于 2019-09-29 15:57:58

1.2K0

发布于 2019-09-29 15:57:58

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在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 |
---------------------------------------------------------------------------