值得反复研读的表连接之ANTI JOIN方式

最新推荐文章于 2024-03-27 06:23:22 发布

DB架构

最新推荐文章于 2024-03-27 06:23:22 发布

阅读量1.1k

点赞数

分类专栏： SQL优化文章标签： sql 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/127475126

版权

SQL优化专栏收录该内容

17 篇文章 2 订阅

订阅专栏

本文介绍了如何通过notin和notexists实现反连接，展示了它们与外连接的等价改写，并探讨了如何控制执行计划，包括NL和HASHJOIN的使用。重点讲解了如何在不同连接方式下调整驱动表以优化查询性能。

摘要由CSDN通过智能技术生成

两表关联只返回主表的数据，而且只返回主表与子表没关联上的数据，这种连接叫反连接。反连接一般就是指not in和not exists

反连接等价改写

not in 与not exists一般情况下也可以进行等价改写。

scott@orclpdb1:orclcdb> set autot off
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> select * from dept where deptno not in (select deptno from emp);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

scott@orclpdb1:orclcdb> select *
  2    from dept
  3   where not exists (select null from emp where dept.deptno = emp.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

scott@orclpdb1:orclcdb>

Note: not in 里面如果有null,整个查询会返回空，而in里面有null,查询不受null影响。

scott@orclpdb1:orclcdb> select * from dept where deptno not in (10,null);

no rows selected

scott@orclpdb1:orclcdb> select * from dept where deptno in (10, null);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

1 row selected.

scott@orclpdb1:orclcdb>

因此在将 not exists 改写为not in时候，要注意null,一般情况下，如果反连接采用not in写法，需要在where条件中剔除null.

select *
from dept
where deptno not in (select deptno from emp where deptno is not null);

not in 和not exists 可以进行互相等价改写，还可以等价地改写为外连接。上面查询可以等价改写为如下写法。

select d.*
  from dept d
  left join emp e
    on d.deptno = e.deptno
 where e.deptno is null;

scott@orclpdb1:orclcdb> select *
  2    from dept
  3   where deptno not in (select deptno from emp where deptno is not null);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

scott@orclpdb1:orclcdb> select d.*
  2    from dept d
  left join emp e
  4      on d.deptno = e.deptno
  5   where e.deptno is null;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

scott@orclpdb1:orclcdb>

控制反连接执行计划

scott@orclpdb1:orclcdb> set autot on
scott@orclpdb1:orclcdb> select * from dept where deptno not in (select deptno from emp);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 218628244

-------------------------------------------------------------------------------------------------
| Id | Operation                             | Name    | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     1 |    23 |     6 (17)| 00:00:01 |
|   1 | MERGE JOIN ANTI NA                   |         |     1 |    23 |     6 (17)| 00:00:01 |
|   2 |   SORT JOIN                           |         |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                   | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|* 5 |   SORT UNIQUE                         |         |    14 |    42 |     4 (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                  | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   5 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")

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

scott@orc

lpdb1:orclcdb>

上例中原始执行计划走的是 MERGE JOIN ANTI NA

现在让其走NL，且不指定驱动表。不起作用。需要给子表中加上where deptno is not null

select /*+use_nl(dept,emp@a)*/
 *
  from dept
 where deptno not in (select /*+qb_name(a)*/
                       deptno
                        from emp where deptno is not null);

scott@orclpdb1:orclcdb> select /*+use_nl(dept,emp@a)*/
2   *
from dept
where deptno not in (select /*+qb_name(a)*/
5                         deptno
6                          from emp where deptno is not null);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1522491139

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |    10   (0)| 00:00:01 |
|   1 | NESTED LOOPS ANTI |      |     1 |    23 |    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 |
---------------------------------------------------------------------------

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

   3 - filter("DEPTNO"="DEPTNO" AND "DEPTNO" IS NOT NULL)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 - SEL$2D5AF68E / DEPT@SEL$1
         U - use_nl(dept,emp@a)

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

scott@orclpdb1:orclcdb>

下面将not in 改写为not exists,加上HINT，再看执行计划。

select /*+use_nl(dept,emp@a)*/
 *
  from dept
 where not exists (select /*+qb_name(a)*/
         null
          from emp
         where emp.deptno = dept.deptno);

scott@orclpdb1:orclcdb> select /*+use_nl(dept,emp@a)*/
2   *
from dept
where not exists (select /*+qb_name(a)*/
5           null
6            from emp
7           where emp.deptno = dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1522491139

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |    10   (0)| 00:00:01 |
|   1 | NESTED LOOPS ANTI |      |     1 |    23 |    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 |
---------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 - SEL$2D5AF68E / DEPT@SEL$1
         U - use_nl(dept,emp@a)

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

scott@orclpdb1:orclcdb>

现在让走NL,让EMP作为驱动表，下面执行计划结果显示并未果、将emp设定为驱动表？是什么原因呢？

原因：反连接等价于外连接+子表连接条件 is null ,当两表关联为外连接时，使用NL无法更改驱动表，由于驱动表被固定为主表。

scott@orclpdb1:orclcdb> select /*+use_nl(dept,emp@a) leading(emp@a)*/
2   *
from dept
4   where not exists (select /*+qb_name(a)*/
5           null
6            from emp
7           where emp.deptno = dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1522491139

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |    10   (0)| 00:00:01 |
|   1 | NESTED LOOPS ANTI |      |     1 |    23 |    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 |
---------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   1 - SEL$2D5AF68E
         U - leading(emp@a)

   2 - SEL$2D5AF68E / DEPT@SEL$1
         U - use_nl(dept,emp@a)

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

scott@orclpdb1:orclcdb>

试着走HASH JOIN ,且EMP作为驱动表。其执行结果DEPT仍然为驱动表。原因是要改变HASH JOIN 的驱动表必须使用swap_join_inputs.

scott@orclpdb1:orclcdb> select /*+use_hash(dept,emp@a) leading(emp@a)*/
2   *
from dept
where not exists (select /*+qb_name(a)*/
5           null
6            from emp
7           where emp.deptno = dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 474461924

---------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     6   (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI    |      |     1 |    23 |     6   (0)| 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 |
---------------------------------------------------------------------------

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

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   1 - SEL$2D5AF68E
         U - leading(emp@a)

   2 - SEL$2D5AF68E / DEPT@SEL$1
         U - use_hash(dept,emp@a)

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

scott@orclpdb1:orclcdb>

使用swap_join_inputs 来更改HASH 连接的驱动表

scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select /*+use_hash(dept,emp@a) swap_join_inputs(emp@a)*/
2   *
from dept
where not exists (select /*+qb_name(a)*/
5           null
          from emp
7           where emp.deptno = dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 152508289

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    23 |     6   (0)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI|      |     1 |    23 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP |    14 |    42 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 - SEL$2D5AF68E / DEPT@SEL$1
         U - use_hash(dept,emp@a)

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

scott@orclpdb1:orclcdb>