CBO 查询转换(3)：结合谓词下推机能（Join Predicate Pushdown）

SQLplusDB

发布于 2022-08-19 20:33:49

2020

发布于 2022-08-19 20:33:49

文章被收录于专栏：Oracle数据库技术

编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页: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.c2
from t2, (select c1, c2 s2 from t1) v1
where t2.c1=v1.c1;  2    3


        S2         C2
---------- ----------
         1          2
         2          2


Execution 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.c2
from t2, (select c1, sum(c2) s2 from t1 group by c1) v1
where t2.c1=v1.c1;  2    3


        S2         C2
---------- ----------
         3          2




Execution 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.c2
from t2, (select c1, sum(c2) s2 from t1 group by c1) v1
where t2.c1=v1.c1;  2    3


        S2         C2
---------- ----------
         3          2


Execution 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 directives
JPPD: 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$1                
 2 - SEL$1                / "T2"@"SEL$1"
 3 - SEL$639F1A6F         / "V1"@"SEL$1"
 4 - SEL$639F1A6F         
 6 - SEL$639F1A6F         / "T1"@"SEL$2"
------------------------------------------------------------
Predicate Information:
----------------------
4 - filter(COUNT(*)>0)
6 - filter("C1"="T2"."C1")

Queryblock “T2”@"SEL

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” 设置成 false

OR
用 /*+ NO_PUSH_PRED */ hint

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-31，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自 SQL和数据库技术微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度

CBO 查询转换(3)：结合谓词下推机能（Join Predicate Pushdown）

CBO 查询转换(3)：结合谓词下推机能（Join Predicate Pushdown）

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐