《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提示，默认情况下谓词推进将会发生。这正是你所要的！可能会有一些反常的案例其中的谓词推进将适得其反，这些案例极其罕见。因此，务必检查执行计划确保谓词推进如预期的发生。