`

《Pro Oracle SQL》CHAPTER2--2.9 Predicate Pushing

阅读更多

Predicate Pushing   谓词推进       (page 69)
    Predicate pushing is used to apply the predicates from a containing query block into a non-mergeable
query block. 
The goal is to allow an index to be used or allow for other filtering of the data set earlier in
the query plan rather than later.  In general, it is always a good idea to filter out rows that aren’t needed
as soon as possible.  Always think: filter early.
     谓词推进用于把谓词从包含它的查询块取出(提前放入)到一不可合并的查询块中。 目的是(允许)使用索引或者在查询计划中较早的使用数据筛选而不是较晚的。一般而言,尽快的筛选出不需要的行是好主意。记住:早筛选。
    A real life example where the downside of filtering late is readily apparent is moving to another city.  
Let’s say you are moving from Portland, Oregon to Jacksonville, Florida.  If you hire a moving company
to pack and move you—and they charge by the pound—it wouldn’t be a very good idea to realize that
you really didn’t need or want 80% of the stuff that was moved.  If you’d just taken the time to check out everything before the movers packed you up in Portland, you could have saved yourself a lot of money!  
    一个现实生活的例子,晚筛选的负面效果是相当明显的,搬家到另一个城市。假设你要从Portland, Oregon 到 Jacksonville, Florida。如果你雇佣一个搬家公司来打包和运送--他们按磅来收费--那可不是个好主意,你意识到不需要或不想要的80%的东西被运送了。如果在搬家公司在Portland打包之前你花时间检查所有的东西,你将为自己节省大笔钱。
    That’s the idea with predicate pushing.  If a predicate can be applied earlier by pushing it into a
non-mergeable query block, there will be less data to carry through the rest of the plan.  Less data means less work.  Less work means less time. 
Listing 2-9 shows the difference between when predicate pushing happens and when it doesn’t.
    这就是谓词推进的思想。如果谓词能被推进到一个非可合并的查询块中越早的应用起来,搬动到计划的其余部分的数据就越少。越少的数据意味的越少的工作。越少的工作意味着越少的时间。 列表2-9展示了谓词推进发生与不发生的对比。
Listing 2-9. Predicate Pushing 
SQL> set autotrace traceonly explain
SQL>
SQL> SELECT e1.last_name, e1.salary, v.avg_salary
  2    FROM employees e1,
  3      (SELECT department_id, avg(salary) avg_salary
  4         FROM employees e2
  5        GROUP BY department_id) v
  6    WHERE e1.department_id = v.department_id
  7      AND e1.salary > v.avg_salary
  8      AND e1.department_id = 60;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2684380651
 
 
------------------------------------------------------------------------------------------
| Id  | Operation                                                  | Name                           | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                                     |     1 |    41 |     3    (0)|
|*  1 |   TABLE ACCESS BY INDEX ROWID         | EMPLOYEES                |     1 |    15 |     1   (0)|
|   2 |    NESTED LOOPS                                    |                                      |     1 |    41 |     3   (0)|
|   3 |       VIEW                                                  |                                      |     1 |    26 |     2   (0)|
|   4 |         HASH GROUP BY                             |                                       |     1 |     7 |     2   (0)|
|   5 |           TABLE ACCESS BY INDEX ROWID | EMPLOYEES                 |     5 |    35 |     2   (0)|
|*  6 |             INDEX RANGE SCAN                    | EMP_DEPARTMENT_IX |     5 |         |     1   (0)|
|*  7 |       INDEX RANGE SCAN                          | EMP_DEPARTMENT_IX |     5 |         |     0   (0)|
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("E1"."SALARY">"V"."AVG_SALARY")
   6 - access("DEPARTMENT_ID"=60)
   7 - access("E1"."DEPARTMENT_ID"=60)

 SQL> SELECT e1.last_name, e1.salary, v.avg_salary
  2    FROM employees e1,
  3      (SELECT department_id, avg(salary) avg_salary
  4         FROM employees e2
  5        WHERE rownum > 1    -- rownum prohibits predicate pushing!   rownum禁止谓词推进
  6        GROUP BY department_id) v
  7    WHERE e1.department_id = v.department_id
  8      AND e1.salary > v.avg_salary
  9      AND e1.department_id = 60;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3834222907
 
---------------------------------------------------------------------------------------
| Id  | Operation                                            | Name                              | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                        |     3 |   123 |     7   (29)|
|*  1 |   HASH JOIN                                        |                                       |     3 |   123 |     7  (29)|
|   2 |      TABLE ACCESS BY INDEX ROWID | EMPLOYEES                  |     5 |    75  |     2   (0)|
|*  3 |        INDEX RANGE SCAN                    | EMP_DEPARTMENT_IX |     5   |         |     1   (0)|
|*  4 |      VIEW                                             |                                       |    11  |   286 |     4  (25)|
|   5 |         HASH GROUP BY                       |                                        |    11  |    77  |     4  (25)|
|   6 |           COUNT                                     |                                        |         |          |              |
|*  7 |             FILTER                                    |                                       |         |          |              |
|   8 |               TABLE ACCESS FULL            | EMPLOYEES                  |   107 |   749 |     3   (0)|
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
       filter("E1"."SALARY">"V"."AVG_SALARY")
   3 - access("E1"."DEPARTMENT_ID"=60)
   4 - filter("V"."DEPARTMENT_ID"=60)
   7 - filter(ROWNUM>1)

    Notice step 6 of the first plan.  The  WHERE department_id = 60 predicate was pushed into the view,
allowing the average salary to only be determined for one department.  When the predicate is not
pushed, as shown in the second plan, the average salary must be computed for every department. Then,
when the outer query block and inner query blocks are joined, all the rows that are not department_id  60
get thrown away.  You can tell from the Rows estimates as well as by the cost of the second plan that the optimizer realizes that having to wait to apply the predicate requires more work and therefore is a more expensive and time-consuming operation.
    注意第一个计划的第6步。谓词WHERE department_id = 60推进入视图,只允许求一个部门(60)的平均薪水。当谓词没有推进,如第二个计划所示,就要对每个部门都求平均薪水。然后再将外部查询块和内部查询块关联,所有不是department_id 60的行都被抛弃。你可以从第二个计划的返回估计(行数)和成本看出,优化器意识到强制推迟应用谓词需要做更多的工作,因此是更贵的,耗时的工作。
    I used a little trick to stop predicate pushing in this example that I want to point out.  The use of the
rownum pseudocolumn in the second query (I added the predicate  WHERE rownum > 1) acted to prohibit
predicate pushing.  As a matter of fact,  rownum not only prohibits predicate pushing but it prohibits view
merging as well. 
Using rownum is like adding the NO_MERGE and NO_PUSH_PRED hints to the query.  In this case, it allowed me to point out the ill effects that occur when predicate pushing doesn’t happen, but I also want to make sure you realize that using  rownum will affect the choices the optimizer has available
when determining the execution plan.  Be careful when you use rownum— it will make any query block it
appears in both non-mergeable and unable to have predicates pushed into it.
    我想要指出我在这个例子中使用了一技巧阻止谓词推进。在第二个查询中使用rownum伪列(我加谓词WHERE rownum > 1)用于禁止谓词推进。事实上,rownum不仅禁止谓词推进也禁止视图合并。 使用rownum就好像在查询中加了NO_MERGE和NO_PUSH_PRED 提示。在本例中,它让我指出谓词推进没有发生时的负面效果,我还想确保你知道:当确定执行计划时,使用rownum将影响优化器的有效选择。当你使用rownum时要小心--它将使得它所在的任何查询块变得非可合并且不能把谓词推进入其中。
    Other than through the use of  rownum or a NO_PUSH_PRED hint, predicate pushing will happen without any special action on your part.  And that’s just what you want!  While there may be a few corner cases (pathological case ) where predicate pushing might be less advantageous, those cases are few and far between.  So, make sure to check execution plans to ensure predicate pushing happens as expected.
     除了通过使用rownum或者NO_PUSH_PRED提示,默认情况下谓词推进将会发生。这正是你所要的!可能会有一些反常的案例 其中的谓词推进将适得其反,这些案例极其罕见。因此,务必检查执行计划确保谓词推进如预期的发生。

1
1
分享到:
评论

相关推荐

    rsql-jpa-specification:Java库将RSQL转换为Spring Data JPA规范和QueryDSL谓词

    将RSQL查询转换为org.springframework.data.jpa.domain.Specification或com.querydsl.core.types.Predicate和support实体关联查询。 Maven仓库 将RSQL的rsql-jpa-spring-boot-starter添加到Spring JPA转换 Maven对r...

    ORACLE执行计划和SQL调优

    2. **Recursive SQL概念**:在执行用户SQL语句的过程中,Oracle可能会自动执行额外的SQL语句,这些额外的语句被称为递归SQL。例如,DDL语句执行时,Oracle会隐式执行递归SQL来更新数据字典信息。用户通常不需要关注...

    A Technical Overview of the HP Oracle Exadata Storage Server - white paper

    HP Oracle Exadata存储服务器是一种高度优化的存储产品,专为Oracle数据库设计,通过利用大规模并行架构,为数据仓库应用提供卓越的I/O和SQL处理性能。Exadata不仅提高了数据库的存储效率,还通过动态存储网格为...

    Inside Microsoft SQL Server 2008 T-SQL Querying

    Delve into the internal architecture of T-SQL—and unveil the power of set-based querying—with comprehensive reference and advice from a highly regarded T-SQL expert and members of Microsoft's SQL ...

    BioPred--Biomedical-Predicate-VerbGroup-Chunker:生物医学文本的谓词词组词块

    版权所有 (c) 2013,Phil Gooch。 本软件根据 2007 年 6 月 29 日的第 3 版获得许可。 有关许可证详细信息,请参阅 LICENSE.txt 文件。 生物医学谓词动词组块(BioPred) 该插件扩展了 ANNIE VP Chunker[1] 以提供...

    ORACLE SQL执行优化

    Oracle SQL执行优化是数据库性能调优的关键环节,主要涉及如何高效地执行SQL查询。本文将深入探讨Oracle常用表连接机制和索引机制,以及如何理解执行计划中的关键概念。 首先,我们要了解ROWID的概念。ROWID是...

    Oracle 11g新特性索引不可见

    "Oracle 11g 新特性索引不可见" Oracle 11g 中引入了一个新特性,称为索引不可见(Index Invisible),它允许 DBA 将索引设置为不可见状态,使得优化器在选择查询计划时忽视该索引,而不影响索引的正常更新。 索引...

    SQL与PL/SQL

    在2011年的ITPUB数据库技术大会上,丁俊先生就“基于Oracle的SQL与PL/SQL开发实践”这一主题进行了深入的探讨。该演讲涵盖了Oracle SQL和PL/SQL两个重要的数据库技术领域的实践经验和案例分析。 ### Oracle SQL实践...

    标准SQL语言基础(Oracle,sql server)

    它被广泛应用于Oracle、SQL Server等数据库管理系统中。本篇文章将详细讲解SQL语言的基础知识,包括其分类、主要语句及其组成部分。 首先,SQL命令可以大致分为六个类别: 1. **属性词(Predicates)**:如ALL、...

    SQL-92标准 中文翻译——定义、记号和约定 (约定)

    根据提供的文件信息,本文将对SQL-92标准中的定义、记号以及约定进行详细的解析。SQL-92是数据库领域的重要标准之一,它为结构化查询语言(Structured Query Language,简称SQL)提供了统一规范,使得不同的数据库...

    ORACLE执行计划和SQL调优.pptx

    2. **Recursive SQL**:当执行SQL语句时,Oracle可能会自动执行额外的SQL语句以完成任务,比如DDL语句导致的数据字典更新。这些隐式调用的SQL称为递归SQL。用户通常无需关注它们,Oracle会在需要时自动处理。 3. **...

    ORACLE执行计划和SQL调优知识概述.pptx

    ORACLE 执行计划和 SQL 调优知识概述 ORACLE 执行计划和 SQL 调优是数据库管理和优化的关键环节,对于提高数据库性能和效率起着至关重要的作用。本篇文章将从背景知识、SQL 调优和工具介绍三个方面对 ORACLE 执行...

    SKIP_UNUSABLE_INDEXES的使用与索引失效解决方法[参照].pdf

    SKIP_UNUSABLE_INDEXES 参数是 Oracle 数据库中的一种参数设置,它的主要用途是当索引为可用状态时保证 SQL 的运行,虽然它保证了系统的健壮性,但是有可能系统没有使用该索引而导致性能的低下。 在 Oracle 中,...

    ORACLE执行计划和SQL调优.ppt

    2. Recursive SQL概念:Recursive SQL是指Oracle在执行用户发出的SQL语句时,需要执行的一些额外的语句,以便成功执行该SQL语句。这些语句称为Recursive calls或Recursive SQL statements。 3. Row Source and ...

    metam:使用Clojure进行文本模型表示的元建模工具

    > {attr-keyword -> predicate-vector}} keyword- {type-keyword -> {attr-keyword -> predicate-vector}}和默认函数var组成。 默认函数通常是一个带有三个参数的多重方法:model元素,type-keyword和attr-keyword。...

Global site tag (gtag.js) - Google Analytics