Query Transformation??? 查询变换?? (Page 61)
??? Prior to the development of the execution plan, a step called query transformation occurs. This step
happens just after a query is checked for syntax and permissions and just before the optimizer computes
cost estimates for the various plan operations it considers when determining the final execution plan.? In
other words, transformation and optimization are two different tasks.
??? 在生成执行计划之前，有一步称之为“查询变换”的步骤将发生。这一步紧接着查询的语法和权限被检查之后，且在优化器计算成本，评估它所考虑的各种计划操作，确定最终执行计划之前执行。换句话说，变换和优化是两个不同的任务。
??? After your query passes the syntactical and permissions checks, the query enters the transform
phase in a set of query blocks.? A query block is defined by the keyword SELECT.? For example, select?
* from employees where department_id = 60 has a single query block.? However, select * from employees where department_id in (select department_id from departments) has two query blocks.? Each query block is either nested within another or interrelated to another in some way.? The way the query is written determines the relationships between query blocks.? It is the query transformer’s main objective to determine if changing the way the query is written will provide a better query plan.
??? 在你的查询通过语法和权限检查之后，查询进入一组查询块集的变换阶段。一查询块被关键字SELECT所定义。例如select * from employees where department_id = 60有一个单独的查询块。然而，select * from employees where department_id in (select department_id from departments) 有两个查询块。每个查询块要么嵌入另一个之中或者以某种方式关联到其他块中。书写查询的方式确定了查询块之间的关系。查询变换的主要目的是确定是否改变所写的查询方式将生成更佳的查询计划。
??? Make sure you caught that last sentence.? The query transformer can, and will, rewrite your query.
This is something you may have never realized.? What you write may not end up being the exact
statement for which the execution plan is developed.? Many times this is a good thing.? The query
transformer knows how the optimizer deals with certain syntax and will do everything it can to render
your SQL in a way that helps the optimizer to come up with the best, most efficient execution plan.
However, the fact that what you write can be changed may mean that a behavior you expected,
particularly the order in which certain parts of the statement occur, doesn’t happen the way you
intended.? Therefore, you really need to understand how query transformation works so that you can
make sure to write your SQL properly to get the behaviors you intend.
??? 务必关注上面的语句。查询变换器能够，也会，重写你的查询。可能你从来没有意识到。你所写的(语句)可能最终不是开发执行计划时(所参考)的准确语句。很多时候这是件好事。查询变换知道优化器如何处理某些语法，然后尽一切可能渲染(加工)你的SQL，帮助优化器生成最佳最有效的执行计划。然而，事实上可能改变你书写的(语句)，意味着你所期望的行为，特别是某些部分的语句的执行顺序，不是按照你的意图发生的。因此，你真正需要理解查询变换如何工作的，以至于你能确保以适当的方式书写你的SQL取得期望的行为。
??? The query transformer may change the way you originally formulated your query as long as the
change does not affect the result set.? Any change that might cause the result set to differ from the
original query syntax will not be considered.? The change that is most often made is to transform
separate query blocks into straight joins.? For example, this statement
??? select * from employees where department_id in (select department_id from departments)
will likely be transformed into this statement
??? select e.* from employees e, departments d where e.department_id = d.department_id?
The result set doesn’t change, but the execution plan choices for the transformed version would be
better from the optimizer’s point of view.
??? 查询变换可能改变最初你书写查询的方式，只要改变不影响结果集。任何可能导致结果集不同于最初的查询语法的改变将不被考虑。最常用的改变是变换分散的查询块成直接的连接。例如，语句
??? select * from employees where department_id in (select department_id from departments)
将可能被变换成语句
??? select e.* from employees e, departments d where e.department_id = d.department_id?
结果集没有改变，但是从优化器的观点来看执行计划选择变换版本(的语句)将更好。
??? Once you learn what to look for, you can usually tell by looking at the execution plan if a
transformation occurs.? You can also execute your query using the NO_QUERY_TRANSFORMATION hint and compare the execution plan from this query with the plan from the query without the hint.? If the two
plans are not the same, the differences can be attributed to query transformation. When using the hint,
all query transformations with the exception of predicate pushing (which I’ll review shortly) will be
prohibited.??
??? 一旦你知道了要查找什么，你就能通过查询执行计划知道是否发生了查询变换。你也可以用NO_QUERY_TRANSFORMATION提示执行你的查询然后将执行计划与不带提示的查询的执行计划相比较。如果两个计划不相同，不同点可归咎于查询变换。当使用了提示，所有除“谓词推进”(我等会讲解)之外的查询变换将被禁止。
??? There are several basic transformations that can be applied to a given query:
- ?View merging
- ?Subquery unnesting
- ?Predicate pushing
- ?Query rewrite with materialized views
??? 有如下几种基本的变换可以应用于给定的查询：
- 视图合并
- 子查询反嵌套
- 谓词推进
- 用物化视图重写查询
?
?