ORACLE谓词推入初解

最新推荐文章于 2021-04-14 06:06:02 发布
原创 最新推荐文章于 2021-04-14 06:06:02 发布 · 4k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#PLSQL #SQL

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

创建视图

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 | | 1 | 44 | 129 (3)| 00:00:02 |
| 1 | NESTED LOOPS | | 1 | 44 | 129 (3)| 00:00:02 |
|* 2 | HASH JOIN | | 1 | 40 | 128 (3)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (2)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | HAO2IDX | 1 | 4 | 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")


接着，我把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 | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 153 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 153 (5)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 36309 | 2340K| 150 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 36309 | 850K| 150 (4)| 00:00:02 |
| 6 | INDEX FAST FULL SCAN | HAO2IDX | 36309 | 141K| 22 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (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 | | 1 | 40 | 128 (2)| 00:00:02 |
| 1 | NESTED LOOPS OUTER | | 1 | 40 | 128 (2)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 36 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | HAOVIEW | 1 | 4 | 126 (2)| 00:00:02 |
| 5 | NESTED LOOPS | | 1 | 24 | 126 (2)| 00:00:02 |
|* 6 | TABLE ACCESS FULL | HAO1 | 1 | 20 | 125 (2)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | HAO2IDX | 1 | 4 | 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 | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 153 (5)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 1 | 86 | 153 (5)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| HAO3 | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HAO3IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | HAOVIEW | 36309 | 2340K| 150 (4)| 00:00:02 |
|* 5 | HASH JOIN | | 36309 | 850K| 150 (4)| 00:00:02 |
| 6 | INDEX FAST FULL SCAN | HAO2IDX | 36309 | 141K| 22 (5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | HAO1 | 36311 | 709K| 125 (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")

可见，对于此种身处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
  2    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 1485
Oracle数据库中的谓词下推和谓词越界是针对查询优化的概念。在Oracle数据库中，谓词下推是一种查询优化技术，旨在通过将过滤条件推送至数据存储层来提高查询性能。它的工作原理是在查询执行过程中，尽早地对数据进行过滤，减少不必要的数据读取和处理。
22 谓词推入(pushing predicate) --优化主题系列
leo0805的博客
10-16 3671
谓词推入(PUSHING PREDICATE) Pushing Predicate(谓词推入)：当SQL语句中包含有不能合并的视图，并且视图有谓词过滤(也就是where过滤条件)，CBO会将where过滤条件推入视图中，这个就叫做谓词推入。谓词推入的主要目的就是让Oracle尽可能早的过滤掉无用的数据，从而提升查询性能。
Oracle谓词推入
u010719917的专栏
11-17 5626
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
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
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...
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 3309
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  不走谓词推入使用方法：/...
Oracle SQL谓词推进、视图合并
07-07 1639
Oracle SQL谓词推进、视图合并
oracle hint 谓词推进,oracle常见hint
weixin_26870313的博客
04-14 1029
与优化器模式相关的hint1.1 /*+ all_rows */ 让优化器启动CBO1.2 /*+ first_rows(n)*/优化器启动CBO，选择会快返回前n行数据的执行计划。与first_rows_n不同，first_rows_n n只能为1,10,100,1000.1.3 /*+ rule */ 启动RBO，与其他hint连用，其他hint一般会失效。与表及索引访问相关的hint2.1 ...
oracle 谓词是什么意思,Oracle谓词推入
weixin_32392583的博客
04-05 1321
在Oracle中，谓词一般就是指where后面的那些过滤条件。而在执行计划中，我们有时候会看到一个信息：VIEWPUSHEDPREDICATE就是传说中的谓词推入。谓词推入是什么意思？就是指视图(VIEW)在做关联之前，会将外面一层的数据放到视图里面先做过滤。一般是将外层关联的索引，放到视图内的表的索引上面来做。在优快云落落的Oracle优化Blog里面，有这样一个优化案例：http://b...
oracle 谓词是什么意思,oracle sql外连接的时候谓词的应用顺序是会影响结果的
weixin_30663789的博客
04-05 290
oracle sql外连接的时候谓词的应用顺序是会影响结果的, 非外连接则没有关系, 不过oracle之所以这样,虽然理解上觉得有点不可思议, 应该也是有其原因的.SELECT COUNT(DISTINCT B.COMPANY_ID)FROM (SELECT *FROM ODS.BAS_TAB_EMPLOYEE_FJYD CWHERE C.END_DATE = TO_DATE('3000-12-3...
oracle hint 谓词推进,SQL Hint都无法解救DB性能时，如何通过视图曲线救国？
weixin_39766109的博客
04-14 341
作者介绍黄浩，现任职于中国惠普，从业十年，始终专注于SQL。十年一剑，十年磨砺。3年通信行业，写就近3万条SQL；5年制造行业，遨游在ETL的浪潮；2年性能优化，厚积薄发自成一家。这个案例的主体是视图，在总结这个案例的时候，我想起了在一次以DB性能为主题的讨论会上，当谈及到视图对性能的影响的时候，主持人抛出一个问题：在数据里面，是不是视图越少性能越好。其实这个问题可以理解成这样：视图会不会影响到性...
oracle hint 谓词推进,一条sql语句优化，请帮忙，涉及谓词推进
weixin_33514300的博客
04-14 2311
select/*+ no_merge(a) push_pred(a) */ a.prtno qq,a.contno,d.prtno,a.appntname,a.appntidno,a.insuredname,a.insuredidno,d.riskcode,(select risknamefrom lmriskwhere '1395381350000' = '1395381350000'and r...
oracle谓词执行顺序研究
sunpang的专栏
10-09 674
1、对于没有建索引的表，where条件谓词的执行顺序为从右到左。 2、对于建有索引的表，且索引的引导列作为谓词的条件，where总是先执行 含有索引引导列作为谓词的条件子句：将不含有索引的表作为驱动表，将通过索引查询的结果作为探测表，然后对行源做嵌套查询，这时where条件谓词的执行顺序就不定了。
Oracle查询转换之连接谓词推入
weixin_34216196的博客
03-12 313
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。连接谓词推入所...
oracle去掉谓词推入,消除谓词推进
weixin_29230059的博客
04-10 912
今天，还是用第一篇博文的例子，看下怎么消除谓词推进。首先先上图，这是一个发生了谓词推进的例子。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...
《基于ORACLE的SQL优化读书》笔记 星形转换/谓词推入/连接因式分解/表移除
JIANGJIAN12345的专栏
04-16 1163
星形转换： 事实表和维度表由外键相连，事实表的外键列上有位图索引。 星形转换核心：将针对各个维度表的限制条件通过等价改写的方式以额外的子查询施加到事实表上。然后通过索引间的位图操作。 允许星形转换： Alter session set star_transformation_enabled =true;   谓词推入： 还是会把视图中定义的SQL当成一个独立的处理单元来处理，但CBO
oracle 阻止谓语前推,oracle琐碎笔记
weixin_36194553的博客
04-09 249
Oracle知识点ps:由于是自己看的所以笔记比较乱,大家谅解CommitrollbackSql核心语句之selectSelct中要用到以下语句From语句Where语句Groupby语句Groupby来进行分组,分组的参数如果有重复的,只返回一个行值.(比如我按id分组如果记录中有两个id为1的记录,这时候只返回一行)Having语句将分组汇总后的查询结果限定为只有该子句的条件为真的数据行...
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、付费专栏及课程。

余额充值