Oracle谓词推入

最新推荐文章于 2025-06-26 17:57:37 发布
最新推荐文章于 2025-06-26 17:57:37 发布
阅读量5.6k 收藏 2
点赞数
分类专栏： oracle


Oracle谓词推入：所谓推入，是把库表中判断的条件推入进视图中进行判断。后续不再对库表进行筛选。

创建视图

create table hao1 as select object_id ,object_name from dba_objects ;
create table hao2 as select object_id ,object_name from dba_objects ;
create table hao3 as select object_id ,object_name from dba_objects ;


create index indx1 on hao1(object_id ) ;
create index indx2 on hao2(object_id ) ;
create index indx3 on hao3(object_id ) ;

create  or replace view haoview as
 select hao1.*
  from  hao1,hao2
 where hao1.object_id = hao2.object_id;

那么对于这样一个简单的查询，可见谓词 hao3.object_name = haoview.object_name 被merge到了view中：

select hao3.object_name
 from  hao3, haoview
 where hao3.object_name = haoview.object_name
   and hao3.object_id   = 999;

---------------------------------------------------------------------------------------
| Id  | Operation                                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |     5 |   855 |   128    (2)| 00:00:02 |
|   1 |  NESTED LOOPS                            |       |     5 |   855 |   128    (2)| 00:00:02 |
|*  2 |   HASH JOIN                              |       |     6 |   948 |   122    (2)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | HAO3  |     1 |    79 |     2    (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | INDX3 |     1 |       |     1    (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                  | HAO1  | 95655 |  7379K|   120    (2)| 00:00:02 |
|*  6 |   INDEX RANGE SCAN                    | INDX2 |     1 |    13 |     1    (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("HAO3"."OBJECT_NAME"="HAO1"."OBJECT_NAME")
   4 - access("HAO3"."OBJECT_ID"=999)
   6 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")



select /*+ no_merge(haoview) */ hao3.object_name
  from  hao3, haoview
 where hao3.object_name = haoview.object_name
   and hao3.object_id   = 999;


接着，我把haoview放到outer join的右侧，这是haoview就属于unmergeable view了，

优化器默认无法将谓词merge进这个haoview中，于是就看到了haoview单独先执行：

select hao3.object_name
  from hao3,haoview
 where hao3.object_name = haoview.object_name(+)
   and hao3.object_id=999;

------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |     6 |   870 |       |   685     (1)| 00:00:09 |
|*  1 |  HASH JOIN OUTER                     |           |     6 |   870 |       |   685     (1)| 00:00:09 |
|   2 |   TABLE ACCESS BY INDEX ROWID      | HAO3    |     1 |    79 |       |     2     (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                   | INDX3   |     1 |       |       |     1     (0)| 00:00:01 |
|   4 |   VIEW                                   | HAOVIEW | 75453 |  4863K|       |   683     (1)| 00:00:09 |
|*  5 |    HASH JOIN                           |           | 75453 |  6778K|  1848K|   683     (1)| 00:00:09 |
|   6 |     INDEX FAST FULL SCAN           | INDX2   | 75452 |   957K|       |    58     (2)| 00:00:01 |
|   7 |     TABLE ACCESS FULL                 | HAO1    | 95655 |  7379K|       |   120     (2)| 00:00:02 |
------------------------------------------------------------------------------------------------

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

   1 - access("HAO3"."OBJECT_NAME"="HAOVIEW"."OBJECT_NAME"(+))
   3 - access("HAO3"."OBJECT_ID"=999)
   5 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")
   
接着，我们来使用这里的hint push_pred强制优化器将谓词merge进view中，可见到“VIEW PUSHED PREDICATE”：


select /*+push_pred(haoview)*/ hao3.object_name
  from hao3,haoview
  where hao3.object_name = haoview.object_name(+)
   and hao3.object_id=999;


----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     2 |    64 |   124     (2)| 00:00:02 |
|   1 |  NESTED LOOPS OUTER         |           |     2 |    64 |   124     (2)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HAO3    |     1 |    30 |     2     (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | INDX3   |     1 |       |     1     (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      | HAOVIEW |     1 |     2 |   122     (2)| 00:00:02 |
|   5 |    NESTED LOOPS          |           |     2 |    70 |   122     (2)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL         | HAO1    |     2 |    60 |   120     (2)| 00:00:02 |
|*  7 |     INDEX RANGE SCAN         | INDX2   |     1 |     5 |     1     (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("HAO3"."OBJECT_ID"=999)
   6 - filter("HAO1"."OBJECT_NAME"="HAO3"."OBJECT_NAME")
   7 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")

于是，会有同学问，那么merge hint能否有同样的效果呢？答案是，对于这种unmergeable view来说，merge hint无效。

select /*+merge(haoview)*/ hao3.object_name
  from hao3,haoview
 where hao3.object_name = haoview.object_name(+)
   and hao3.object_id=999;

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     2 |   192 |       |   421     (2)| 00:00:06 |
|*  1 |  HASH JOIN OUTER         |           |     2 |   192 |       |   421     (2)| 00:00:06 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HAO3    |     1 |    30 |       |     2     (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | INDX3   |     1 |       |       |     1     (0)| 00:00:01 |
|   4 |   VIEW                 | HAOVIEW | 86350 |  5565K|       |   418     (2)| 00:00:06 |
|*  5 |    HASH JOIN             |           | 86350 |  2951K|  1440K|   418     (2)| 00:00:06 |
|   6 |     INDEX FAST FULL SCAN     | INDX2   | 86351 |   421K|       |    54     (2)| 00:00:01 |
|   7 |     TABLE ACCESS FULL         | HAO1    | 86350 |  2529K|       |   119     (1)| 00:00:02 |
------------------------------------------------------------------------------------------------

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

   1 - access("HAO3"."OBJECT_NAME"="HAOVIEW"."OBJECT_NAME"(+))
   3 - access("HAO3"."OBJECT_ID"=999)
   5 - access("HAO1"."OBJECT_ID"="HAO2"."OBJECT_ID")

可见，对于此种身处outger join右侧的view来说，merge hint已经无能为力了。

 

---后续忽略

1、创建视图

SQL> create view v_p as select * from emp where deptno=10;
View created.
SQL> select * from v_p   ;--视图走全表
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   117 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |   117 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1109  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
          
SQL> select empno,dname from v_p,dept
      where v_p.deptno=dept.deptno; - -此语句有谓词推入
Execution Plan
----------------------------------------------------------
Plan hash value: 568005898

--------------------------------------------------------------------------------
--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
--------

|   0 | SELECT STATEMENT             |         |     3 |    54 |     4   (0)| 00
:00:01 |

|   1 |  NESTED LOOPS  （嵌套循环、效率一般不高）              |         |     3 |    54 |     4   (0)| 00
:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00
:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
:00:01 |

|*  4 |   TABLE ACCESS FULL          | EMP     |     3 |    21 |     3   (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT"."DEPTNO"=10) --筛选语句从emp.deptno=10推送至dept表的deptno。
   4 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        662  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

使用hint，强制sql走hash连接（是否使用谓词推入要看具体的需求、慎用！！）
SQL> select /*+use_hash(v_p,dept)*/empno,dname from v_p,dept
  2    where v_p.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2263032238
--------------------------------------------------------------------------------
--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT             |         |     3 |    54 |     5  (20)| 00
:00:01 |

|*  1 |  HASH JOIN                   |         |     3 |    54 |     5  (20)| 00
:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00
:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
:00:01 |

|*  4 |   TABLE ACCESS FULL          | EMP     |     3 |    21 |     3   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPT"."DEPTNO")
   3 - access("DEPT"."DEPTNO"=10)
   4 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        662  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

【Oracle】Oracle谓词下推、谓词越界详解
tttzzzqqq2018的博客
09-30 1491
Oracle数据库中的谓词下推和谓词越界是针对查询优化的概念。在Oracle数据库中，谓词下推是一种查询优化技术，旨在通过将过滤条件推送至数据存储层来提高查询性能。它的工作原理是在查询执行过程中，尽早地对数据进行过滤，减少不必要的数据读取和处理。
Oracle知识总结（三）| Oracle表查询变换优化思路
xiaobinhxb的博客
08-12 1013
Oracle表查询变换优化思路，包括子查询非嵌套、视图合并、谓词推入、让半连接和反连接作驱动表
什么是谓词下推？
周勇政的博客
06-26 1006
谓词下推（Predicate Pushdown，简称 PPD）是数据库查询优化中的一项关键技术，其核心思想是将 SQL 语句中的过滤条件（谓词）尽可能地推送到数据处理的最底层（如存储层或更接近数据源的操作节点），以减少数据传输量和计算开销。
oracle 谓词是什么意思,Oracle谓词推入
weixin_32392583的博客
04-05 1322
在Oracle中，谓词一般就是指where后面的那些过滤条件。而在执行计划中，我们有时候会看到一个信息：VIEWPUSHEDPREDICATE就是传说中的谓词推入。谓词推入是什么意思？就是指视图(VIEW)在做关联之前，会将外面一层的数据放到视图里面先做过滤。一般是将外层关联的索引，放到视图内的表的索引上面来做。在优快云落落的Oracle优化Blog里面，有这样一个优化案例：http://b...
ORACLE谓词推入初解
FLEXES的ORACLE之路
08-07 4059
oracle谓词推入： 1、创建视图 SQL> create view v_p as select * from emp where deptno=10; View created. SQL> select * from v_p   ;--视图走全表 Execution Plan -----------------------------------------------------
oracle查出来做转换,Oracle查询转换之连接谓词推入
weixin_32920055的博客
04-07 167
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。连接谓词推入所带来的基于索引的嵌套循...
22 谓词推入(pushing predicate) --优化主题系列
leo0805的博客
10-16 3673
谓词推入(PUSHING PREDICATE) Pushing Predicate(谓词推入)：当SQL语句中包含有不能合并的视图，并且视图有谓词过滤(也就是where过滤条件)，CBO会将where过滤条件推入视图中，这个就叫做谓词推入。谓词推入的主要目的就是让Oracle尽可能早的过滤掉无用的数据，从而提升查询性能。
SQL 优化 第七章 查询转换 2视图合并和谓词推入
songjian1104的专栏
06-09 540
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 2视图合并和谓词推入 2.1视图合并 2.1.1 视图合并环境准备 create table tests1 as select * from dba_objects; create ta...
Oracle NO_PUSH_PRED 不使用谓词推入
有梦就别怕痛
06-08 3312
Oracle Hint No PUSH PREDThe NO_PUSH_PRED hint instructs the optimizer not to push a join predicate into the view.当察看执行计划发现有 view pushed predicate， 并且很确定这一步是使得PLAN cost 很高。 那么此时可以考虑使用hint  不走谓词推入使用方法：/...
谓词推入(pushing predicate)
liujinwei2005的专栏
06-23 996
谓词推入(PUSHINGPREDICATE) PushingPredicate(谓词推入)：当SQL语句中包含有不能合并的视图，并且视图有谓词过滤(也就是where过滤条件)，CBO会将where过滤条件推入视图中，这个就叫做谓词推入。谓词推入的主要目的就是让Oracle尽可能早的过滤掉无用的数据，从而提升查询性能。 下面举个例子(本例基于Scott用户Oracle11gR2): create or replace view emp1 as select /*+NO_MERGE */ena..
oracle 谓词推入失效,SQL优化案例——谓词推入导致SQL变慢
weixin_36071371的博客
04-05 597
涉及知识：1.SQL优化三大件2.谓词推入的弊端问题现象：在发版测试的过程中，发现有一条预算相关的语句十分缓慢。这里简单的把SQL贴一下。SELECT purapplyrpttb.pk_org_vFROM (select tb_pur.pk_pur_apply_h....FROM ((select hPurApplyTb.pk_pur_apply_h pk_pur_apply_h...FROM e...
oracle谓词推进测试
敖尔其楞的专栏
11-04 731
可合并视图，不可合并视图，谓词推进测试  create table t3 as select * from dba_tables;  create table t4 as select * from dba_users;  create table t5 as select * from dba_objects; 对于没有外连接的测试 SQL> select * from (s
一个谓词推入案例
weixin_33752045的博客
03-26 180
ITpub 上有个帖子 http://www.itpub.net/thread-1852068-1-1.html 生产数据库版本  10.2.0.4测试数据库版本  10.2.0.1sql在生产库运行就使用了谓词推进，效率很高，只要3s，但是在测试库没有使用谓词推进，需要6分30s。大家帮忙看看如何优化或者强制使用谓词推进，使其在测试库效率高一些生产库执行计划： 测试库执行计划： 原...
Oracle谓词推入导致sql很慢的优化
czq3015的博客
09-23 377
优化前的SQL： select * from (select ta.fl_securityid,ta.fc_securitymarket,ta.fc_securitycode,ta.fl_investmenttypes,Fl_Balancetype,fl_circulation, ta.fc_settlecurrency, f...
oracle中的子查询展开和谓词推入
Roger的数据库专栏
04-28 1583
详见原文博客链接地址：about subquery unnest/push pred
oracle去掉谓词推入,消除谓词推进
weixin_29230059的博客
04-10 918
今天，还是用第一篇博文的例子，看下怎么消除谓词推进。首先先上图，这是一个发生了谓词推进的例子。sql：SELECTCOUNT(1) AS countFROM (SELECT a.ykf272, a.akb020, a.akb021,a.yka094, a.ake005, a.yke936, a.ykf282, a.aac003, a.aac001, a.aac002, a.akc225, a.ak...
谓词推入
weixin_34404393的博客
06-28 229
--Join Predicate Pushdowncreate table emp1 as select * from scott.emp create table emp2 as select * from scott.emp create index idx_emp1 on emp1(empno);create index idx_emp2 on emp2(empno);...
Oracle查询转换之连接谓词推入
weixin_34216196的博客
03-12 314
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。连接谓词推入所...
oracle谓词越界
最新发布
07-02
在Oracle数据库中，**谓词越界（predicate out of range）** 是一个与查询优化和统计信息相关的性能问题。它通常发生在查询条件中的值超出了表列的统计范围，导致优化器无法准确估算查询结果集的大小，从而可能选择不理想的执行计划。 ### 问题解释 当SQL查询中使用了某个列上的过滤条件（例如 `WHERE column = value`），而该值超出了该列在统计信息中的最大或最小值时，就会触发“谓词越界”现象。这种情况下，优化器可能会低估或高估返回的行数[^1]。 例如，假设某列 `col1` 的统计信息显示其最大值为 100，但查询使用了 `WHERE col1 = 200`，此时 Oracle 就会标记该谓词为“out of range”。 此行为会影响以下方面： - **执行计划的选择**：优化器基于错误的行数预估可能导致索引扫描与全表扫描之间的误判。 - **性能下降**：不准确的统计信息会导致次优执行计划，影响查询响应时间。 ### 解决方案 #### 1. 更新统计信息 最直接的方法是重新收集相关对象的统计信息，确保它们反映最新的数据分布情况。 ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME'); ``` 如果表有分区，应考虑对特定分区进行统计更新： ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', PARTNAME=>'PARTITION_NAME'); ``` #### 2. 使用直方图 对于数据分布不均匀的列，创建频率直方图可以帮助优化器更准确地评估谓词的选择性。 ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', METHOD_OPT=>'FOR COLUMNS SIZE AUTO COLUMN_NAME'); ``` #### 3. 固定统计信息（适用于静态数据） 如果某些列的数据很少变化，可以考虑手动锁定统计信息以防止自动收集过程覆盖这些值。 ```sql EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME'); ``` #### 4. 使用动态采样（Dynamic Sampling） 在查询执行前，Oracle 可以通过动态采样临时获取缺失的统计信息，有助于优化器做出更好的决策。 ```sql SELECT /*+ DYNAMIC_SAMPLING(4) */ * FROM table_name WHERE column = value; ``` #### 5. 查询重写 如果某些查询频繁使用超出范围的值，考虑将这些值映射到已知的有效范围内，或者引入额外的条件来引导优化器。 --- ### 性能监控建议 可以通过以下方式识别是否发生了谓词越界问题： - 检查执行计划中的 `cardinality` 是否明显偏离实际返回行数。 - 分析 AWR 报告中 SQL 执行效率偏低的语句。 - 启用 10053 事件跟踪优化器决策过程，查看谓词如何被处理。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值