执行计划-6：推入子查询

数据库平台优化 2020-02-26

678

我提出了“子操作按先后顺序执行，递归向下”的解释执行计划的基本策略，但是

在这个系列的第5部分，我们看了一些需要额外注意的”第一个子操作先执行”的例子。在第6部分中，我们将继续探讨一个原则—“子查询推入”，其中“第一个子操作先执行”可能会导致错误的结论。

Access or Filter

下面这两个执行计划的基本形状是很相似的，我们很容易假设，我们应该把这两种情况下的操作顺序基本上解释为“从下到上”。

--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    19 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |    19 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |    11 |       |
|*  4 |     TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
|*  5 |      INDEX RANGE SCAN          | MM_PK   |    10 |       |     2 |
--------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    19 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MIN_MAX |     1 |    19 |     2 |
|*  2 |   INDEX UNIQUE SCAN           | MM_PK   |     1 |       |     1 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
|*  4 |     INDEX RANGE SCAN          | MM_PK   |     2 |       |     2 |
-------------------------------------------------------------------------

实际上”第一个子操作先执行”对于第一个计划来说是正确的，它会从下到上执行，但是对于第二个计划来说是行不通的，由于优化器呈现的是“推入”过的筛选子查询，所以计划的形状被扭曲了，也就是说，在尽可能早的时间运行。很难从计划的主体中看出这是否已经发生了，你真的需要检查计划的谓词部分，甚至可以参考原始语句来理解发生了什么。

这里按顺序展示了上面两个执行计划的谓词部分:

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"= (SELECT
              MAX("MM2"."ID_CHILD") FROM "MIN_MAX" "MM2" 
              WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1))
   4 - filter("STATUS"=1)
   5 - access("MM2"."ID_PARENT"=100)

 Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"=1)
       filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM
              "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1 AND
              ("MM2"."ID_CHILD"=TRUNC(:B1) OR "MM2"."ID_CHILD"=TRUNC(:B2+1))))
   3 - filter("STATUS"=1)
   4 - access("MM2"."ID_PARENT"=100)
       filter("MM2"."ID_CHILD"=TRUNC(:B1) OR
              "MM2"."ID_CHILD"=TRUNC(:B2+1))

注意第2行出现的关键差异——在两个执行计划中的操作都是索引唯一扫描。第一个计划只报告一个访问谓词(包含一个子查询)，而第二个计划同时显示一个访问谓词(它由针对索引列的简单谓词组成)和一个筛选谓词(它由一个子查询组成)。

对于一个索引的操作，访问谓词告诉我们范围扫描的起始和结束的值，过滤谓词告诉我们需要怎么针对每一个在范围内的索引条目的进行验证。看过了谓词部分的不同之后我们可以解释计划：

First plan：

第5行我们对mm_pk索引进行范围扫描，第4行访问min_max表的某些行，在第3行聚合这些行并且找到id_child列的最大值，之后在第2行我们使用这个值对mm_pk进行索引唯一扫描，最后第1行中我们访问表min_max并找到我们所需要的这一行。操作的顺序为:5，4，3，2，1，0.

Second plan：

第2行我们使用提供的谓词对mm_pk进行索引唯一扫描，之后在第4行中使用第一个索引扫描返回的值进行范围扫描，再之后在第3行中访问min_max表查找是否有符合条件的行，如果有就在第1行返回最多一行的rowid。操作的顺序为：2，4，3，1，0。

下面展示原始查询语句可能更易于理解执行的过程。这些语句只是为了说明这小节表达的观点，所以并不需要理解语句为什么要这么写。

select
small_vc
 from min_max mm1
 where mm1.id_parent = 100
 and mm1.id_child = (
 select max(mm2.id_child)
 from   min_max mm2
 where  mm2.id_parent = 100
 and    status = 1
)
;

 select
 small_vc
 from min_max mm1
 where mm1.id_parent = 100
 and mm1.id_child = 1 
 and exists (
 select /*+ no_unnest push_subq */
                       null
 from   min_max mm2
 where  mm2.id_parent = 100
 and    (
           mm2.id_child = trunc(mm1.id_child) 
        or mm2.id_child = trunc(mm1.id_child + 1) 
       ) 
 and    status = 1 
) 
;

有了前面的两个查询语句，就更容易看到第一个可以通过先运行子查询然后使用结果来驱动主查询;第二个查询必须先运行主查询，然后每个步骤都需要停止并检测子查询的存在性。

你会注意到我在第二个查询优化器使用hint，阻止优化器做任何转换。我使用了no_unnest块来阻止子查询展开，然后我告诉优化器尽可能早的执行子查询;后一种hint影响了执行计划的形状(即操作的顺序)。如果我把hint改为no_push_subq(阻止子查询推入)，计划就会变为：

------------------------------------------------------------------------
 | Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |         |     1 |    19 |     5 |
 |*  1 |  FILTER                      |         |       |       |       |
 |   2 |   TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    19 |     2 |
 |*  3 |    INDEX UNIQUE SCAN         | MM_PK   |     1 |       |     1 |
 |*  4 |   TABLE ACCESS BY INDEX ROWID| MIN_MAX |     1 |    11 |     3 |
 |*  5 |    INDEX RANGE SCAN          | MM_PK   |     2 |       |     2 |
------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST */ 0 FROM
          "MIN_MAX" "MM2" WHERE "MM2"."ID_PARENT"=100 AND "STATUS"=1 AND
          ("MM2"."ID_CHILD"=TRUNC(:B1) OR "MM2"."ID_CHILD"=TRUNC(:B2+1))))
   3 - access("MM1"."ID_PARENT"=100 AND "MM1"."ID_CHILD"=1)
   4 - filter("STATUS"=1)
   5 - access("MM2"."ID_PARENT"=100)
       filter("MM2"."ID_CHILD"=TRUNC(:B1) OR
           "MM2"."ID_CHILD"=TRUNC(:B2+1))

有了这个更改，“第一个子操作先执行”规则又可以工作了:第1行的过滤操作调用第2行来返回行，然后调用第4行来决定是否保留该行。我们可以通过检查谓词部分来确认这种解释，在该部分中，我们可以看到子查询的文本现在作为过滤器谓词出现在第1行。

通过使用' outline '选项作为对dbms_xplan调用的格式化参数，您可以在检查已推入的子查询方面获得一些帮助，因为这将允许您查看大纲/SQL计划基线中是否有与计划相关的push_subq()提示。

这个例子向您展示了推入子查询的潜在好处——当我们阻塞推入时，Oracle直到它访问了表并获得了我们不需要的行之后才运行子查询;另一方面，当我们推入子查询时，Oracle会在索引项可用时立即运行它，从而避免了冗余表访问。

Conclusion

在所有这些计划与“第一个子操作先执行”不匹配的例子中，我们已经看到了推入的子查询——在某些情况下，我不得不在SQL中加入一个显式的hint来创建我想要演示的计划类型。当你试图为包含多个子查询的语句解释执行计划时，请注意推入的子查询可能会导致“正常”形状的失真。你可以仔细通过谓词部分检查,能否看到一些子查询文本作为一个过滤谓词出现。

原文链接:

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-6-pushed-subqueries/

原文作者:

Jonathan Lewis

最后修改时间：2020-02-26 12:52:59

文章转载自数据库平台优化，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

执行计划-6：推入子查询

评论