Oracle Hint(提示）之 PUSH_SUBQ、NO_PUSH_SUBQ
  IE5LYMWlmdvL 2023年11月25日 19 0

作用和使用方法

PUSH_SUBQ：尽可能早的执行不能做子查询展开的子查询,不能子查询展开时，通常最后执行，当子查询能早些过滤条件时，应该让其先执行。
NO_PUSH_SUBQ：不能展开时最后执行。

使用语法：

写在子查询中：SELECT /*+ PUSH_SUBQ */ 

写在外层：
select /*+ PUSH_SUBQ(@子查询代码块名称) */ 
 from ...
where (子查询)

其中子查询代码块名称:也可以结合子查询qb_name定义代码块名称。

使用版本：ORACLE 10G及以上。

测试验证

创建测试表：

create table t1 as select * from dba_objects WHERE ROWNUM<=1000;
create table t2 as select * from dba_objects;
CREATE INDEX IDX_T2_ID ON t2(object_id);

create table t1_temp
as
select * from t1 where rownum<=100;

create table t1_temp_2
as
select * from t1 where rownum<=1;

exec dbms_stats.gather_table_stats(null,'t1');
exec dbms_stats.gather_table_stats(null,'t2');
exec dbms_stats.gather_table_stats(null,'t1_temp');
exec dbms_stats.gather_table_stats(null,'t1_temp_2');

测试SQL语句：

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select 1
          from t1_temp tt
         where (tt.object_id = t1.object_id
            or tt.object_id = t1.data_object_id ) );

执行计划如下：
------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |  1854 (100)|    106 |00:00:00.15 |    4176 |
|*  1 |  FILTER             |         |      1 |        |            |    106 |00:00:00.15 |    4176 |
|*  2 |   HASH JOIN         |         |      1 |   1000 |   354   (1)|   1000 |00:00:00.05 |    1272 |
|   3 |    TABLE ACCESS FULL| T1      |      1 |   1000 |     6   (0)|   1000 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS FULL| T2      |      1 |  87194 |   348   (1)|  87194 |00:00:00.02 |    1257 |
|*  5 |   TABLE ACCESS FULL | T1_TEMP |   1000 |      2 |     3   (0)|    106 |00:00:00.10 |    2904 |
------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))

由于子查询中使用了OR关联，不能做子查询展开。
因此将子查询的执行放在最后。

下面分析应用场景：

1.尽可能早执行：缩减数据量。

通过在子查询中使用Push_subq提示，目的是让子查询提前执行，达到缩减数据量的目的。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select /*+ Push_subq */1
          from t1_temp tt
         where (tt.object_id = t1.object_id
            or tt.object_id = t1.data_object_id ) );

调整后的执行计划如下：

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |   109 (100)|    106 |00:00:00.09 |    2964 |
|   1 |  NESTED LOOPS                |           |      1 |     50 |   106   (0)|    106 |00:00:00.09 |    2964 |
|   2 |   NESTED LOOPS               |           |      1 |     50 |   106   (0)|    106 |00:00:00.09 |    2952 |
|*  3 |    TABLE ACCESS FULL         | T1        |      1 |     50 |     6   (0)|    106 |00:00:00.09 |    2927 |
|*  4 |     TABLE ACCESS FULL        | T1_TEMP   |   1000 |      2 |     3   (0)|    106 |00:00:00.09 |    2904 |
|*  5 |    INDEX RANGE SCAN          | IDX_T2_ID |    106 |      1 |     1   (0)|    106 |00:00:00.01 |      25 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T2        |    106 |      1 |     2   (0)|    106 |00:00:00.01 |      12 |
-----------------------------------------------------------------------------------------------------------------

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

   3 - filter( IS NOT NULL)
   4 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

可以看到，子查询与T1表提前关联，可以提前缩减关联后的数据量到106行。
之后在与T2表关联时，由于数据量的减少，可以直接利用T2上的关联索引，提升查询效率，避免全表扫描的问题。

同理，可以使用另一种hint写法：

select /*+ Push_subq(@sub1) */t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select /*+ qb_name(sub1) */1
          from t1_temp tt
         where (tt.object_id = t1.object_id
            or tt.object_id = t1.data_object_id ) );

如果使用其反义：no_Push_subq 。禁止子查询提前执行。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select /*+ no_Push_subq */1
          from t1_temp tt
         where (tt.object_id = t1.object_id
            or tt.object_id = t1.data_object_id ) );

则无法提前利用子查询缩减数据量。

------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |  1854 (100)|    106 |00:00:00.14 |    4176 |
|*  1 |  FILTER             |         |      1 |        |            |    106 |00:00:00.14 |    4176 |
|*  2 |   HASH JOIN         |         |      1 |   1000 |   354   (1)|   1000 |00:00:00.05 |    1272 |
|   3 |    TABLE ACCESS FULL| T1      |      1 |   1000 |     6   (0)|   1000 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS FULL| T2      |      1 |  87194 |   348   (1)|  87194 |00:00:00.02 |    1257 |
|*  5 |   TABLE ACCESS FULL | T1_TEMP |   1000 |      2 |     3   (0)|    106 |00:00:00.09 |    2904 |
------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))

由于T1表数据较多，仍然需要全表访问T2表。

2.针对性的控制过滤性好的子查询先执行。

假设有两个子查询，但过滤效果不一样。t1_temp_2 表较小，过滤效果很好。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select 1
          from t1_temp tt
         where (tt.object_id = t1.object_id
            or tt.object_id = t1.data_object_id ) )
   and exists (select 1
          from t1_temp_2 tt2
         where (tt2.object_id = t1.object_id
            or tt2.object_id = t1.data_object_id ) );

默认情况下，数据库仍然会将子查询放在最后去执行。

--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |  1857 (100)|      1 |00:00:00.17 |    4381 |
|*  1 |  FILTER             |           |      1 |        |            |      1 |00:00:00.17 |    4381 |
|*  2 |   HASH JOIN         |           |      1 |   1000 |   354   (1)|   1000 |00:00:00.08 |    1265 |
|   3 |    TABLE ACCESS FULL| T1        |      1 |   1000 |     6   (0)|   1000 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS FULL| T2        |      1 |  87194 |   348   (1)|  87194 |00:00:00.04 |    1250 |
|*  5 |   TABLE ACCESS FULL | T1_TEMP   |   1000 |      2 |     3   (0)|    106 |00:00:00.09 |    2904 |
|*  6 |   TABLE ACCESS FULL | T1_TEMP_2 |    106 |      1 |     3   (0)|      1 |00:00:00.01 |     212 |
--------------------------------------------------------------------------------------------------------

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

   1 - filter(( IS NOT NULL AND  IS NOT NULL))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))
   6 - filter(("TT2"."OBJECT_ID"=:B1 OR "TT2"."OBJECT_ID"=:B2))

达不到提前缩减数据量的目的。

下面分别测试对两个子查询提前执行的效果。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select /*+ Push_subq */1
          from t1_temp tt
         where tt.object_id = t1.object_id or tt.object_id = t1.data_object_id)
	and exists (select 1
          from t1_temp_2 tt2
         where tt2.object_id = t1.object_id or tt2.object_id = t1.data_object_id);
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |   184 (100)|      1 |00:00:00.09 |    3149 |
|*  1 |  FILTER                       |           |      1 |        |            |      1 |00:00:00.09 |    3149 |
|   2 |   NESTED LOOPS                |           |      1 |     50 |   106   (0)|    106 |00:00:00.09 |    2937 |
|   3 |    NESTED LOOPS               |           |      1 |     50 |   106   (0)|    106 |00:00:00.09 |    2931 |
|*  4 |     TABLE ACCESS FULL         | T1        |      1 |     50 |     6   (0)|    106 |00:00:00.09 |    2920 |
|*  5 |      TABLE ACCESS FULL        | T1_TEMP   |   1000 |      2 |     3   (0)|    106 |00:00:00.09 |    2904 |
|*  6 |     INDEX RANGE SCAN          | IDX_T2_ID |    106 |      1 |     1   (0)|    106 |00:00:00.01 |      11 |
|   7 |    TABLE ACCESS BY INDEX ROWID| T2        |    106 |      1 |     2   (0)|    106 |00:00:00.01 |       6 |
|*  8 |   TABLE ACCESS FULL           | T1_TEMP_2 |    106 |      1 |     3   (0)|      1 |00:00:00.01 |     212 |
------------------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   4 - filter( IS NOT NULL)
   5 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - filter(("TT2"."OBJECT_ID"=:B1 OR "TT2"."OBJECT_ID"=:B2))

提前执行T1_TEMP子查询，将数据量缩减到106行。之后循环106次关联T2表。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select 1
          from t1_temp tt
         where tt.object_id = t1.object_id or tt.object_id = t1.data_object_id)
	and exists (select /*+ Push_subq */1
          from t1_temp_2 tt2
         where tt2.object_id = t1.object_id or tt2.object_id = t1.data_object_id);
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |   184 (100)|      1 |00:00:00.01 |    2022 |
|*  1 |  FILTER                       |           |      1 |        |            |      1 |00:00:00.01 |    2022 |
|   2 |   NESTED LOOPS                |           |      1 |     50 |   106   (0)|      1 |00:00:00.01 |    2020 |
|   3 |    NESTED LOOPS               |           |      1 |     50 |   106   (0)|      1 |00:00:00.01 |    2019 |
|*  4 |     TABLE ACCESS FULL         | T1        |      1 |     50 |     6   (0)|      1 |00:00:00.01 |    2016 |
|*  5 |      TABLE ACCESS FULL        | T1_TEMP_2 |   1000 |      1 |     3   (0)|      1 |00:00:00.01 |    2000 |
|*  6 |     INDEX RANGE SCAN          | IDX_T2_ID |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       3 |
|   7 |    TABLE ACCESS BY INDEX ROWID| T2        |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       1 |
|*  8 |   TABLE ACCESS FULL           | T1_TEMP   |      1 |      2 |     3   (0)|      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   4 - filter( IS NOT NULL)
   5 - filter(("TT2"."OBJECT_ID"=:B1 OR "TT2"."OBJECT_ID"=:B2))
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))

提前执行T1_TEMP_2子查询，将数据量缩减到1行。之后只需要循环访问1次T2表。执行效率较好。

因此，可以有针对过滤性较好的子查询先执行，
通过关联子查询降低数据量，提升查询效率。

3.不提前执行时，按照子查询出现顺序访问子查询。

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select 1
          from t1_temp tt
         where tt.object_id = t1.object_id or tt.object_id = t1.data_object_id)
   and exists (select 1
          from t1_temp_2 tt2
         where tt2.object_id = t1.object_id or tt2.object_id = t1.data_object_id);
--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |  1857 (100)|      1 |00:00:00.16 |    4381 |
|*  1 |  FILTER             |           |      1 |        |            |      1 |00:00:00.16 |    4381 |
|*  2 |   HASH JOIN         |           |      1 |   1000 |   354   (1)|   1000 |00:00:00.07 |    1265 |
|   3 |    TABLE ACCESS FULL| T1        |      1 |   1000 |     6   (0)|   1000 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS FULL| T2        |      1 |  87194 |   348   (1)|  87194 |00:00:00.03 |    1250 |
|*  5 |   TABLE ACCESS FULL | T1_TEMP   |   1000 |      2 |     3   (0)|    106 |00:00:00.09 |    2904 |
|*  6 |   TABLE ACCESS FULL | T1_TEMP_2 |    106 |      1 |     3   (0)|      1 |00:00:00.01 |     212 |
--------------------------------------------------------------------------------------------------------

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

   1 - filter(( IS NOT NULL AND  IS NOT NULL))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))
   6 - filter(("TT2"."OBJECT_ID"=:B1 OR "TT2"."OBJECT_ID"=:B2))

默认情况下，会按照子查询出现顺序访问。

修改出现顺序：

select t1.object_name,t2.object_name
  from t1, t2
 where t1.object_id = t2.object_id
   and exists (select 1
          from t1_temp_2 tt2
         where tt2.object_id = t1.object_id or tt2.object_id = t1.data_object_id)
   and exists (select 1
          from t1_temp tt
         where tt.object_id = t1.object_id or tt.object_id = t1.data_object_id);
--------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |  1857 (100)|      1 |00:00:00.08 |    3267 |
|*  1 |  FILTER             |           |      1 |        |            |      1 |00:00:00.08 |    3267 |
|*  2 |   HASH JOIN         |           |      1 |   1000 |   354   (1)|   1000 |00:00:00.08 |    1265 |
|   3 |    TABLE ACCESS FULL| T1        |      1 |   1000 |     6   (0)|   1000 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS FULL| T2        |      1 |  87194 |   348   (1)|  87194 |00:00:00.04 |    1250 |
|*  5 |   TABLE ACCESS FULL | T1_TEMP_2 |   1000 |      1 |     3   (0)|      1 |00:00:00.01 |    2000 |
|*  6 |   TABLE ACCESS FULL | T1_TEMP   |      1 |      2 |     3   (0)|      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------

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

   1 - filter(( IS NOT NULL AND  IS NOT NULL))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(("TT2"."OBJECT_ID"=:B1 OR "TT2"."OBJECT_ID"=:B2))
   6 - filter(("TT"."OBJECT_ID"=:B1 OR "TT"."OBJECT_ID"=:B2))

不同的出现顺序，也会影响先后执行顺序。最终影响过滤效果，导致执行效率差异。
可以有针对性的控制过滤性好的子查询先书写。来达到缩减数据量的目的。

知识总结

PUSH_SUBQ可以让子查询尽可能提前执行，在分析子查询时，如果能确定其具有很好的过滤作用，可以利用该提示优先执行子查询，来测试是否能提升执行效率。

【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
  1. 分享：
最后一次编辑于 2023年11月25日 0

暂无评论

推荐阅读
  COAWCwhiWpsR   2023年12月10日   13   0   0 oraclerac新增节点oraclerac删除节点rac新增节点rac删除节点
  MvB0DW3BzXHQ   2023年12月12日   19   0   0 子查询MySQLMySQL子查询数据数据
  nf1vV6jNCjlb   2023年12月09日   20   0   0 子查询嵌套子查询插入数据插入数据嵌套
  VhtxyaVzvLBv   2023年12月07日   25   0   0 子查询示例代码MySQLMySQL子查询示例代码
  xaeiTka4h8LY   9天前   16   0   0 joinoracle
  KcsvWDGBewHK   2023年12月12日   20   0   0 子查询字段Server子查询Server字段
  LJ090R1n8lhs   2023年12月08日   21   0   0 子查询解决方案MySQLMySQL子查询解决方案
  5lPzlfK4LLoX   2023年12月07日   50   0   0 子查询SQLMySQLMySQL子查询SQL
  GlkGUhBxsw3T   2023年12月11日   17   0   0 字段字段SQL性能优化sql优化数据性能优化sql优化SQL数据
  KGlmCU54TQex   2023年12月12日   9   0   0 oracleoraclebashbash
  pgz3sOTQddjN   2023年12月11日   15   0   0 子查询MySQLMySQL子查询数据数据
  0SnbOly3LC5t   2023年12月10日   12   0   0 oracleoracleMySQLMySQL数据一致性数据库数据一致性数据库
  0SnbOly3LC5t   2023年12月10日   24   0   0 备份文件oracleoracle备份文件增量备份MySQLMySQL数据库增量备份数据库
  xaeiTka4h8LY   2024年03月22日   57   0   0 Databaseoracle数据库
  xEIKQOiGayQx   2023年12月08日   21   0   0 子查询自定义自定义MySQLMySQL子查询
  IanmX1lTBbhs   2023年12月07日   19   0   0 DGoracleoracleoggoggDG
  xaeiTka4h8LY   2024年03月22日   34   0   0 Databaseoracle数据库
  2zFGj4r9I43q   2023年12月07日   32   0   0 子查询MySQLMySQL子查询结果集结果集
IE5LYMWlmdvL
IE5LYMWlmdvL
作者其他文章 更多
postgresql 数据库面试题及知识梳理

2023-12-06

slowlog汇总报告工具slow-log-summary

2023-12-05

MySQL 数据库面试题及知识梳理

2023-12-05

Linux CPU软中断问题诊断之基础概念

2023-12-02

Oracle Hint(提示）之INDEX_SS、INDEX_SS_ASC、INDEX_SS_DESC

2023-11-30

Oracle Hint(提示）之INDEX_COMBINE

2023-11-28

Oracle Hint(提示）之LEADING

2023-11-26

Oracle Hint(提示）之ORDERED

2023-11-26

Oracle Hint(提示）之 PUSH_SUBQ、NO_PUSH_SUBQ

2023-11-25

insert死锁与唯一索引-mysql

2023-11-24

最新推荐 更多
k3s和k8s对比，应该选择哪个

2024-05-20

apisix~集成服务发现注册中心

2024-05-20

apisix~helm方式的部署到k8s

2024-05-17

k8s中如何控制pod运行的节点

2024-05-17

有了k8s还需要gateway网关，nacos配置中心吗

2024-05-17

apisix~jwt-auth插件

2024-05-17

带你熟悉CCE集群增强型CPU管理策略enhanced-static

2024-05-17

解码方法 II。‘A‘ -＞ 1，‘B‘ -＞ 2，...‘Z‘ -＞ 26。*是1-9，不包含0。

2024-05-17

找到字符串中所有字母异位词。 给定两个字符串 s 和 p，找到 s 中所有 p 的 异位词 的子串，返回这些子串的起始索引。不考虑答案输出的顺序。

2024-05-17

有四种诗的韵律分别为: AABB、ABAB、ABBA、AAAA。

2024-05-17

一个子序列的消除规则

2024-05-17

Emlog漏洞————Emlog数据库备份与导入功能导致后台getshell

2024-05-17

python3的json数据库-TinyDB效率篇

2024-05-17

有人在贴吧问phpmyadmin如何设置插入的时候默认插入1条记录

2024-05-17

群友提问的面试题

2024-05-17

批量更新数据问题

2024-05-17

手撕二叉搜索树——模拟实现

2024-05-17

利用完全二叉树的性质，如何创建一个大根堆和一个小根堆？

2024-05-17

Mysql安装说明

2024-05-17

【WebLogic】Oracle WebLogic中间件生命周期表

2024-05-17

常用标签
数据数据数据数据javalinuxlinuxpythonCentOSredisJava区块链技术python区块链信息系统字符串MySQL资讯项目管理mysql教程MySQL