Oracle中的半联结和反联结详解

前端技术 2023/09/09 Oracle

当两张表进行联结的时候，如果表1中的数据行是否出现在结果集中需要根据表2中出现或不出现至少一个相匹配的数据行来判断，这种情况就会发生半联结；而反联结便是半联结的补集，它们会作为数据库中常见的联结方法如NESTED LOOPS，MERGE SORT JOIN，HASH JOIN的选项出现。

实际上半联结和反联结本身也可以被认同是两种联结方法；在CBO优化模式下，优化器能够根据实际情况灵活的转换执行语句从而实现半联结和反联结方法，毕竟没有什么SQL语法可以显式的调用半联结和反联结，它们只是SQL语句满足某些条件时优化器可以选择的选项而已，不过仍然有必要深入这两种选项在特定情况下带来的性能优势。

半联结

半联结通常都发生在使用含有IN和EXISTS的相关子查询的时候，=ANY的用法与IN相同，所以也会出现发生半联结的情况；不过也是有例外的，在11gR2版本中，优化器不会为任何包含在OR分支中的子查询选择半联结，这也是现在官档中唯一明确标识的限制条件，来看几种发生场景：

复制代码 代码如下:

-- 使用IN关键字的相关子查询 => 发生NESTED LOOPS半联结
SQL> select department_name
  2  from hr.departments dept
  3  where department_id in (select department_id from hr.employees emp);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2605691773

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(\"DEPARTMENT_ID\"=\"DEPARTMENT_ID\")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        742  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

-- 使用EXISTS关键字的相关子查询 => 发生NESTED LOOPS半联结
SQL> select department_name
  2  from hr.departments dept where exists
  3  (select null from hr.employees emp where emp.department_id = dept.department_id);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2605691773

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |                   |    10 |   190 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(\"EMP\".\"DEPARTMENT_ID\"=\"DEPT\".\"DEPARTMENT_ID\")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        742  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

-- 谓语中使用了OR分支中的EXISTS子查询 => 禁用半联结
SQL> select department_name
  2  from hr.departments dept
  3  where 1=2 OR exists
  4  (select null from hr.employees emp where emp.department_id = dept.department_id);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 440241596

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    27 |   432 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |                   |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |     2 |     6 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM \"HR\".\"EMPLOYEES\" \"EMP\" WHERE
              \"EMP\".\"DEPARTMENT_ID\"=:B1))
   3 - access(\"EMP\".\"DEPARTMENT_ID\"=:B1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        742  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

本文地址：https://www.stayed.cn/item/25077

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案