CBO 查询转换(3)：结合谓词下推机能（Join Predicate Pushdown）
编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
【免责声明】本公众号文章仅代表个人观点，与任何公司无关。
咱们来接下来探讨一下 View 和表结合，这时候 CBO 会如何转换用户的 SQL 呢。
通常的一个 View 和表去结合，View 里没什么特殊处理的话，就直接去使用表作 JOIN 即可。
drop table t1 purge;drop table t2 purge;create table t1(c1 number, c2 number not null);create table t2(c1 number, c2 number not null);insert into t1 values (1,1);insert into t1 values (1,2);insert into t1 values (2,2);insert into t1 values (3,2);insert into t2 values (1,2);commit;SQL> select v1.s2,t2.c2from t2, (select c1, c2 s2 from t1) v1where t2.c1=v1.c1; 2 3S2 C2---------- ----------1 22 2Execution Plan----------------------------------------------------------Plan hash value: 2959412835---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 24 | 6 (0)| 00:00:01 ||* 1 | HASH JOIN | | 2 | 24 | 6 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| T2 | 1 | 6 | 3 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| T1 | 2 | 12 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("T2"."C1"="C1")
但是，那要是 VIEW 里面存在 distinct，或者 Group by 的情况下就有点复杂了，View 就没法展开和表去进行结合。
这种情况下，CBO 就会想展开困难的话，那要是把 View 和表结合的谓词下推到 View 中是不是会产生什么神奇效果呢。
于是乎，11g 开始结合谓词下推机能（Join Predicate Pushdown）机能也登场了。
结合谓词下推机能（Join Predicate Pushdown）
首先还是通过下面的例子，看看没用到这个机能的情况下 SQL 执行的情况。
SQL> select v1.s2,t2.c2from t2, (select c1, sum(c2) s2 from t1 group by c1) v1where t2.c1=v1.c1; 2 3S2 C2---------- ----------3 2Execution Plan----------------------------------------------------------Plan hash value: 1660256317-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 32 | 7 (15)| 00:00:01 ||* 1 | HASH JOIN | | 1 | 32 | 7 (15)| 00:00:01 || 2 | TABLE ACCESS FULL | T2 | 1 | 6 | 3 (0)| 00:00:01 || 3 | VIEW | | 1 | 26 | 4 (25)| 00:00:01 || 4 | HASH GROUP BY | | 1 | 6 | 4 (25)| 00:00:01 || 5 | TABLE ACCESS FULL| T1 | 2 | 12 | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("T2"."C1"="V1"."C1")
是不是可以看出，这种情况下，SQL 还是完全按照用户的逻辑来实现的呢。
View 内的查询完成后，结果集和表在去结合，在结合时用到了谓词。
那么，结合谓词下推机能是什么样子呢。
SQL> select /*+ PUSH_PRED(V1) */ v1.s2,t2.c2from t2, (select c1, sum(c2) s2 from t1 group by c1) v1where t2.c1=v1.c1; 2 3S2 C2---------- ----------3 2Execution Plan----------------------------------------------------------Plan hash value: 3439651121--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 19 | 6 (0)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 19 | 6 (0)| 00:00:01 || 2 | TABLE ACCESS FULL | T2 | 1 | 6 | 3 (0)| 00:00:01 || 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 3 (0)| 00:00:01 ||* 4 | FILTER | | | | | || 5 | SORT AGGREGATE | | 1 | 6 | | ||* 6 | TABLE ACCESS FULL | T1 | 2 | 12 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - filter(COUNT(*)>0)6 - filter("C1"="T2"."C1")
是不是谓词 “C1”=“T2”.“C1” 被下推到了 View 里去了，View 里做了 filter 的结果集再来和表去结合。这个机能被使用时，最大的标识时 VIEW PUSHED PREDICATE 操作。
继续通过 10053 EVENT Trace 再去探究一下，CBO 是怎样实现的。
JPPD: Applying transformation directivesJPPD: Checking validity of push-down in query block SEL$1 (#1)JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.JPPD: Performing join predicate push-down (final phase) from query block SEL$1 (#1) to query block SEL$2 (#2)JPPD: Pushing predicate "T2"."C1"="V1"."C1" <<<<<<<<★from query block SEL$1 (#1) to query block SEL$2 (#2) <<<<<<<<★Final query after transformations:******* UNPARSED QUERY IS *******SELECT /*+ PUSH_PRED ("V1") */ "V1"."S2" "S2","T2"."C2" "C2" FROM "U1"."T2" "T2", (SELECT SUM("T1"."C2") "S2" FROM "U1"."T1" "T1" WHERE "T1"."C1"="T2"."C1" HAVING COUNT(*)>0) "V1"Plan Table============-------------------------------------------+-----------------------------------+| Id | Operation | Name | Rows | Bytes | Cost | Time |-------------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 6 | || 1 | NESTED LOOPS | | 1 | 19 | 6 | 00:00:01 || 2 | TABLE ACCESS FULL | T2 | 1 | 6 | 3 | 00:00:01 || 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 3 | 00:00:01 || 4 | FILTER | | | | | || 5 | SORT AGGREGATE | | 1 | 6 | | || 6 | TABLE ACCESS FULL | T1 | 2 | 12 | 3 | 00:00:01 |-------------------------------------------+-----------------------------------+Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------1 - SEL$12 - SEL$1 / "T2"@"SEL$1"3 - SEL$639F1A6F / "V1"@"SEL$1"4 - SEL$639F1A6F6 - SEL$639F1A6F / "T1"@"SEL$2"------------------------------------------------------------Predicate Information:----------------------4 - filter(COUNT(*)>0)6 - filter("C1"="T2"."C1")
Queryblock “T2”@"SEL$1"的谓词 “T2”.“C1”=“V1”.“C1” 下推到 Queryblock “T1”@“SEL$2”，SQL 在内部被自动转换成下面的样子。
SELECT /*+ PUSH_PRED (“V1”) / “V1”.“S2” “S2”,“T2”.“C2” “C2” FROM “U1”.“T2” “T2”, (SELECT SUM(“T1”.“C2”) “S2” FROM “U1”.“T1” “T1” WHERE “T1”.“C1”=“T2”.“C1” HAVING COUNT()>0) “V1”上面展示了结合谓词下推机能，这个机能具体是好是坏的评价还要根据实际情况来看，这里就没法过多的展开。
关于如何关闭这个机能，有以下方法：
隐含参数 “_push_join_predicate” 设置成 falseOR用 /*+ NO_PUSH_PRED */ hint
后续文章更加精彩，欢迎关注本公众号或访问【阅读原文】。
——End——
专注于技术不限于技术！
用碎片化的时间，一点一滴地提高数据库技术和个人能力。
欢迎关注！
数据库基础系列（从基础了解数据库）：
通过寄存服务来“理解”Oracle数据库基本体系结构和动作流程
手把手系列（帮助个人技术成长）：
CBO 查询转换系列（了解Oracle优化器）
原文作者：SQL和数据库技术；如有侵权，请联系管理员删除。
上一篇：
Oracle性能调查之ASH（一）
请登录后发表评论
登录
全部评论
