性能调优：连接谓词下推(Join Predicate Pushdown(JPPD))

我们的文章会在微信公众号Oracle恢复实录和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Optimizer Transformation: Join Predicate Pushdown
优化器转换：连接谓词下推

此文章来至公司BLOG网站一遍关于连接谓词下推的一篇文章，主要是为上一篇文章性能调优：连接谓词下推(Join Predicate Pushdown(JPPD))做一个基础的介绍，详细内容如下，文章的原初见文章最后。

Happy New Year to all of our readers! We hope you all had a great holiday season. We start the new year by continuing our series on Optimizer transformations. This time it is the turn of Predicate Pushdown. I would like to thank Rafi Ahmed for the content of this blog.
祝我们所有的读者新年快乐！希望你们都度过了一个美好的假期。我们在新的一年继续我们的优化器转换系列。这次轮到谓词下推。我要感谢 Rafi Ahmed 为本博客提供的内容。

Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
通常，视图不能与基于索引的嵌套循环（即索引访问）连接，因为与基表不同，视图没有定义索引。视图只能使用三种方法与表连接：哈希连接、嵌套循环连接和排序合并连接。

介绍

The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.
连接谓词下推（JPPD）转换允许视图与基于索引的嵌套循环连接方法进行连接，这可能提供更优的选择。在连接谓词下推转换中，视图仍然是一个独立的查询块，但它包含从包含它的查询块下推到视图中的连接谓词。因此，视图变成相关的，必须为外部查询块的每一行进行评估。这些下推的连接谓词一旦进入视图内部，就会在视图内的基表上开辟新的索引访问路径；这允许视图与基于索引的嵌套循环连接方法进行连接，从而使优化器能够选择高效的执行计划。

The join predicate pushdown transformation is not always optimal. The join predicate pushed-down view becomes correlated and it must be evaluated for each outer row; if there is a large number of outer rows, the cost of evaluating the view multiple times may make the nested-loop join suboptimal, and therefore joining the view with hash or sort-merge join method may be more efficient.
连接谓词下推转换并不总是最优的。下推连接谓词的视图变成相关的，必须为每个外部行进行评估；如果外部行数量很大，多次评估视图的成本可能使嵌套循环连接次优，因此使用哈希连接或排序合并连接方法连接视图可能更高效。

The decision whether to push down join predicates into a view is determined by evaluating the costs of the outer query with and without the join predicate pushdown transformation under Oracle’s cost-based query transformation framework. The join predicate pushdown transformation applies to both non-mergeable views and mergeable views and to pre-defined and inline views as well as to views generated internally by the optimizer during various transformations. The following shows the types of views on which join predicate pushdown is currently supported.
是否将连接谓词下推到视图中是由 Oracle 基于成本的查询转换框架下评估有无连接谓词下推转换的外部查询成本决定的。连接谓词下推转换适用于不可合并视图和可合并视图，以及预定义视图和内联视图，以及优化器在各种转换过程中内部生成的视图。以下显示了当前支持连接谓词下推的视图类型。

• UNION ALL/UNION view
• Outer-joined view
• Anti-joined view
• Semi-joined view
• DISTINCT view
• GROUP-BY view

• UNION ALL/UNION 视图
• 外连接视图
• 反连接视图
• 半连接视图
• DISTINCT 视图
• GROUP-BY 视图

示例

Consider query A, which has an outer-joined view V. The view cannot be merged, as it contains two tables, and the join between these two tables must be performed before the join between the view and the outer table T4.
考虑查询 A，它有一个外连接视图 V。该视图不能被合并，因为它包含两个表，这两个表之间的连接必须在视图与外部表 T4 之间的连接之前执行。

SELECT T4.unique1, V.unique3
FROM T_4K T4,
           (SELECT T10.unique3, T10.hundred, T10.ten
            FROM T_5K T5, T_10K T10
            WHERE T5.unique3 = T10.unique3) V
WHERE T4.unique3 = V.hundred(+)
AND       T4.ten = V.ten(+)
AND       T4.thousand = 5;

The following shows the non-default plan for query A generated by disabling join predicate pushdown.
以下显示了通过禁用连接谓词下推为查询 A 生成的非默认计划。

When query A undergoes join predicate pushdown, it yields query B. Note that query B is expressed in a non-standard SQL and shows an internal representation of the query.
当查询 A 经过连接谓词下推时，它产生查询 B。注意查询 B 以非标准 SQL 表示，显示了查询的内部表示。

SELECT T4.unique1, V.unique3
FROM T_4K T4,
           (SELECT T10.unique3, T10.hundred, T10.ten
            FROM T_5K T5, T_10K T10
            WHERE T5.unique3 = T10.unique3
            AND T4.unique3 = V.hundred(+)
            AND T4.ten = V.ten(+)) V
WHERE T4.thousand = 5;

The execution plan for query B is shown below.
查询 B 的执行计划如下所示。

In the execution plan BX, note the keyword ‘VIEW PUSHED PREDICATE’ indicates that the view has undergone the join predicate pushdown transformation. The join predicates (shown here in red) have been moved into the view V; these join predicates open up index access paths thereby enabling index-based nested-loop join of the view. With join predicate pushdown, the cost of query A has come down from 62 to 32.
在执行计划 BX 中，注意关键字 ‘VIEW PUSHED PREDICATE’ 表示视图已经过连接谓词下推转换。连接谓词（这里用红色显示）已被移入视图 V；这些连接谓词开辟了索引访问路径，从而实现了视图的基于索引的嵌套循环连接。通过连接谓词下推，查询 A 的成本从 62 降低到 32。

As mentioned earlier, the join predicate pushdown transformation is cost-based, and a join predicate pushed-down plan is selected only when it reduces the overall cost.
如前所述，连接谓词下推转换是基于成本的，只有当它降低总体成本时，才会选择连接谓词下推计划。

Consider another example of a query C, which contains a view with the UNION ALL set operator.
考虑另一个查询 C 的例子，它包含一个带有 UNION ALL 集合运算符的视图。

SELECT R.unique1, V.unique3
FROM T_5K R,
           (SELECT T1.unique3, T2.unique1+T1.unique1
            FROM T_5K T1, T_10K T2
            WHERE T1.unique1 = T2.unique1
            UNION ALL
            SELECT T1.unique3, T2.unique2
            FROM G_4K T1, T_10K T2
            WHERE T1.unique1 = T2.unique1) V
WHERE R.unique3 = V.unique3 and R.thousand < 1;

The execution plan of query C is shown below.
查询 C 的执行计划如下所示。

In the above, ‘VIEW UNION ALL PUSHED PREDICATE’ indicates that the UNION ALL view has undergone the join predicate pushdown transformation. As can be seen, here the join predicate has been replicated and pushed inside every branch of the UNION ALL view. The join predicates (shown here in red) open up index access paths thereby enabling index-based nested loop join of the view.
在上面，’VIEW UNION ALL PUSHED PREDICATE’ 表示 UNION ALL 视图已经过连接谓词下推转换。可以看出，这里的连接谓词已被复制并下推到 UNION ALL 视图的每个分支中。连接谓词（这里用红色显示）开辟了索引访问路径，从而实现了视图的基于索引的嵌套循环连接。

Consider query D as an example of join predicate pushdown into a distinct view. We have the following cardinalities of the tables involved in query D: Sales (1,016,271), Customers (50,000), and Costs (787,766).
考虑查询 D 作为连接谓词下推到 distinct 视图的示例。查询 D 涉及的表基数如下：Sales (1,016,271)、Customers (50,000) 和 Costs (787,766)。

SELECT C.cust_last_name, C.cust_city
FROM customers C,
           (SELECT DISTINCT S.cust_id
            FROM sales S, costs CT
            WHERE S.prod_id = CT.prod_id and CT.unit_price > 70) V
WHERE C.cust_state_province = 'CA' and C.cust_id = V.cust_id;

The execution plan of query D is shown below.
查询 D 的执行计划如下所示。

As shown in XD, when query D undergoes join predicate pushdown transformation, the expensive DISTINCT operator is removed and the join is converted into a semi-join; this is possible, since all the SELECT list items of the view participate in an equi-join with the outer tables. Under similar conditions, when a group-by view undergoes join predicate pushdown transformation, the expensive group-by operator can also be removed.
如 XD 所示，当查询 D 经过连接谓词下推转换时，昂贵的 DISTINCT 运算符被移除，连接被转换为半连接；这是可能的，因为视图的所有 SELECT 列表项都参与与外部表的等值连接。在类似条件下，当 group-by 视图经过连接谓词下推转换时，昂贵的 group-by 运算符也可以被移除。

With the join predicate pushdown transformation, the elapsed time of query D came down from 63 seconds to 5 seconds.
通过连接谓词下推转换，查询 D 的执行时间从 63 秒降低到 5 秒。

Since distinct and group-by views are mergeable views, the cost-based transformation framework also compares the cost of merging the view with that of join predicate pushdown in selecting the most optimal execution plan.
由于 distinct 和 group-by 视图是可合并视图，基于成本的转换框架在选择最优执行计划时也会比较合并视图的成本与连接谓词下推的成本。

总结

We have tried to illustrate the basic ideas behind join predicate pushdown on different types of views by showing example queries that are quite simple. Oracle can handle far more complex queries and other types of views not shown here in the examples. Again many thanks to Rafi Ahmed for the content of this blog post.
我们试图通过展示相当简单的示例查询来说明不同类型视图背后连接谓词下推的基本思想。Oracle 可以处理更复杂的查询和示例中未显示的其他类型的视图。再次感谢 Rafi Ahmed 为本博客文章提供的内容。

来自 https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle

——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
联系二维码