强烈需要掌握的查询变换之子查询非嵌套

最新推荐文章于 2024-03-21 09:10:10 发布

数据架构

最新推荐文章于 2024-03-21 09:10:10 发布

阅读量289

点赞数

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

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

版权

SQL优化专栏收录该内容

17 篇文章

订阅专栏

本文讨论了如何通过子查询非嵌套技术，即展平where子查询中的IN/NOTIN等操作，来消除SQL中的FILTER，从而优化执行计划并避免性能问题。实例分析了两种SQL写法，并提供了等价改写后的执行计划对比。

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

子查询非嵌套（subquery Unnesting）: 当where子查询中有in、not in、exits、not exists等，CBO会尝试将子查询展开（unnest）,从而消除FILTER。这个过程叫作子查询非嵌套。子查询非嵌套的目的就是消除FILTER.

select ename, deptno
  from emp
 where exists (select deptno
          from dept
         where dname = 'CHICAGO'
           and emp.deptno = dept.deptno
        union
        select deptno
          from dept
         where loc = 'CHICAGO'
           and dept.deptno = emp.deptno);

scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select ename, deptno
2    from emp
where exists (select deptno
4            from dept
5           where dname = 'CHICAGO'
6             and emp.deptno = dept.deptno
        union
8          select deptno
9            from dept
10           where loc = 'CHICAGO'
11             and dept.deptno = emp.deptno);

ENAME          DEPTNO
---------- ----------
ALLEN              30
WARD               30
MARTIN             30
BLAKE              30
TURNER             30
JAMES              30

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2705207488

------------------------------------------------------------------------------------------
| Id | Operation                      | Name    | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     5 |    45 |     9   (0)| 00:00:01 |
|* 1 | FILTER                        |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL            | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT UNIQUE                  |         |     2 |    48 |     2   (0)| 00:00:01 |
|   4 |    UNION-ALL                   |         |       |       |            |          |
|* 5 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|* 6 |      INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|* 7 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|* 8 |      INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE
              "DEPT"."DEPTNO"=:B1 AND "DNAME"='CHICAGO')UNION (SELECT "DEPTNO" FROM "DEPT"
              "DEPT" WHERE "DEPT"."DEPTNO"=:B2 AND "LOC"='CHICAGO')))
   5 - filter("DNAME"='CHICAGO')
   6 - access("DEPT"."DEPTNO"=:B1)
   7 - filter("LOC"='CHICAGO')
   8 - access("DEPT"."DEPTNO"=:B1)

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

scott@orclpdb1:orclcdb>

等价改写如下：

select ename, deptno
  from emp
 where exists (select 1
          from (select deptno
                  from dept
                 where dname = 'CHICAGO'
                union
                select deptno
                  from dept
                 where loc = 'CHICAGO') a
         where a.deptno = emp.deptno);

scott@orclpdb1:orclcdb> select ename, deptno
2    from emp
3   where exists (select 1
4            from (select deptno
                  from dept
6                   where dname = 'CHICAGO'
7                  union
                select deptno
9                    from dept
10                   where loc = 'CHICAGO') a
11           where a.deptno = emp.deptno);

ENAME          DEPTNO
---------- ----------
ALLEN              30
WARD               30
MARTIN             30
BLAKE              30
TURNER             30
JAMES              30

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4243948922

------------------------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     5 |   110 |     9   (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI       |      |     5 |   110 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | EMP |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   VIEW                |      |     2 |    26 |     6   (0)| 00:00:01 |
|   4 |    SORT UNIQUE        |      |     1 |    24 |     6   (0)| 00:00:01 |
|   5 |     UNION-ALL         |      |       |       |            |          |
|* 6 |      TABLE ACCESS FULL| DEPT |     1 |    13 |     3   (0)| 00:00:01 |
|* 7 |      TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="EMP"."DEPTNO")
   6 - filter("DNAME"='CHICAGO')
   7 - filter("LOC"='CHICAGO')

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

scott@orclpdb1:orclcdb>

等价改写后，消除了FILTER，由于FILTER的驱动表是固定的，一旦驱动表被固定，那么执行计划也就被固定，固然会引起性能问题。要想提升性能必须改写SQL语句。故此一定要消除FILTER.

开发DBA很大一部分职责：必须保证SQL上线之后，每个SQL语句的执行计划都是可控的，这样才能尽可能避免系统中SQL越跑越慢。

接下来继续对上述SQL进行等价改写

scott@orclpdb1:orclcdb> select ename, deptno
  2    from emp
 where deptno in (select deptno
  4                      from dept
  5                     where dname = 'CHICAGO'
                  union
  7                    select deptno
  8                      from dept
  9                     where loc = 'CHICAGO');

ENAME          DEPTNO
---------- ----------
ALLEN              30
WARD               30
MARTIN             30
BLAKE              30
TURNER             30
JAMES              30

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2705207488

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     9 |    81 |     9   (0)| 00:00:01 |
|*  1 |  FILTER                        |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL            | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT UNIQUE                  |         |     2 |    48 |     2   (0)| 00:00:01 |
|   4 |    UNION-ALL                   |         |       |       |            |          |
|*  5 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1
              AND "DNAME"='CHICAGO')UNION (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE
              "DEPTNO"=:B2 AND "LOC"='CHICAGO')))
   5 - filter("DNAME"='CHICAGO')
   6 - access("DEPTNO"=:B1)
   7 - filter("LOC"='CHICAGO')
   8 - access("DEPTNO"=:B1)


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

scott@orclpdb1:orclcdb>

执行计划中如果产生了FILTER，一般是无法通过HINT消除的，一定要注意执行计划中的FILTER.

哪些SQL写法容易产生FILTER呢？

当子查询语句含有exists或not exists时，子查询中有固化子查询关键词（union/union all / start connect by/rownum /cube / rollup）,那么执行计划就很容易产生FILTER.