《Pro Oracle SQL》Chapter8--8.8 Performance Tuning

Performance Tuning     性能调优      (page 266)
    Analytic functions are very useful in tuning complex SQL statements. Inter-row referencing, aggregation at multiple levels, and nth-row access are a few of the important features analytic functions provide. For example, a typical query fetching both aggregated and non-aggregated rows must perform a self-join. In a data warehouse environments, due to the sheer size of the tables involved, these self-joins can be cost prohibitive . 
    对于复杂SQL语句的优化分析函数很有用。行间引用，在多层次聚合，访问第n行都是分析函数所提供的重要特性。例如，典型的同时取聚合和非聚合的查询必须执行自连接。在数据仓库环境，由于涉及的表都非常巨大，这些自连接将是成本过高的 。
    The efficiency that analytics bring to the table often makes them useful tools in rewriting queries
that do not perform well. In turn, however, you can sometimes face the need to tune an analytic
function. To that end , there are some useful things to know about analytic functions and execution
plans, analytics and predicates, and strategies for indexing.
    数学分析带给表的效率使得他们成为重写性能不高查询的有用工具。 然而，你有时需要调试分析函数。为了这个目的 ，关于分析函数和执行计划，数学分析和谓词，还有(建立)索引策略，这些有用的事情必须了解。

Execution Plans   执行计划
    Analytic function introduces few new operations in the SQL execution plan. Presence of the keywords
WINDOW SORT indicate that the SQL statement utilizes an analytic function. In this section, I will review
the mechanics of analytic function execution. 
    分析函数在SQL执行计划中引入了几个新的操作。关键字WINDOW SORT的出现表明SQL语句使用了分析函数。 在本节中，我将回顾分析函数执行的机制。
    Listing 8-21 shows a typical execution plan of a SQL statement. Execution of this plan starts at step 4
and works its way outwards to step 1:
4.   Table SALES_FACT is accessed using Full Table Scan access path. 
3.   Filter predicates on Product, Country, Region, and Year column are applied filtering required
rows.
2.   Analytic functions are applied over the filtered rows from the step 3.
1.   Predicate on Week column applied after the execution of these analytic functions.
    列表8-21展示了一典型的SQL语句的执行计划。该计划的执行开始于步骤4且向外运行到步骤1。
4.  表SALES_FACT使用全表扫描访问路径。
3.  在Product, Country, Region, and Year column 上的过滤器谓词过滤所需的行。
2. 分析函数应用于第三步过滤取得的行。
1. 在Week列的谓词应用在这些分析函数执行之后。

■ NOTE  The Cost Based Optimizer does not assign or calculate a cost for analytic functions (as of 11gR2). The cost of the SQL statement is calculated without considering the cost of analytic functions. 
■ 注意 基于成本的优化器不会对分析函数赋予或者计算成本值(如版本11gR2)。SQL语句的成本计算没有考虑分析函数的成本。
Listing 8-21.  Execution Plan
----------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                                |     5 |   290 |   581   (3)|
|*  1 |  VIEW                           | MAX_5_WEEKS_VW     |     5 |   290 |   581   (3)|
|   2 |   WINDOW SORT              |                                |     5 |   330 |   581   (3)|
|*  3 |    TABLE ACCESS FULL     | SALES_FACT              |     5 |   330 |   580   (3)|
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("WEEK"<14)
   3 - filter("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia' AND
              "REGION"='Australia' AND "YEAR"=2000)

Predicates    谓词
    Predicates must be applied on the tables as early as possible to reduce the result set for better
performance. Rows must be filtered earlier so that analytic functions are applied to relatively fewer rows.
Predicate safety is an important consideration in executing analytic functions as not all predicates can
be applied beforehand.
    谓词必须尽可能早的应用于表中减少结果集和更好的性能。行集必须较早的筛选，这样的话分析函数就作用于较少的行集上。运行分析函数时谓词安全是要重点考虑的，因为不是所有的谓词都能提前应用的。
    In the Listing 8-22, a view called max_5_weeks_vw is defined and a SQL statement is accessing the
view with the predicates on Country, Product, Region, Year, and Week columns. The execution plan
shows that the following filter predicates are applied in step 3:
     在列表8-22中，定义了视图max_5_weeks_vw且一SQL语句通过在Country, Product, Region, Year, 和Week 列上的谓词访问视图。执行计划显示如下筛选谓词应用于第三步：
filter(("PRODUCT"='Xtend Memory' AND "COUNTRY"='Australia'
AND "REGION"='Australia' AND "YEAR"=2000)) 
 
    However, the predicate  "WEEK"<14 is not applied in step 3, and is only applied in step 1, indicating
that the predicate is applied after executing the analytic functions in step 2’s  WINDOW SORT  step. All
supplied predicates except that on the Week column were pushed into the view. Filtering of those
predicates then took place before executing the analytic functions. 
    然而，谓词“WEEK”<14没有应用于第三步，而只应用于第一步,说明该谓词在执行第二步分析函数之后才起作用。除了Week列之外的谓词都推进到视图中了。这些谓词的筛选发生在执行分析函数之前。
    Predicates on partitioning columns are applied before executing analytic functions, as generally
speaking, predicates on partitioning column can be pushed safely into the view. But columns in the
order-by-clause of the analytic function syntax can’t be pushed safely as the inter-row references need
access to other rows in the same partitions, even if those rows are not returned in the final result set.
    在分区列上的谓词在执行分析函数之前应用，一般而言，在分区列上的谓词可被安全的推进入视图。但是在分析函数句法的order-by子句中的谓词不能安全的推进，因为行间引用需要访问相同分区的其他行，即使这些行没有在最终的结果集中返回。

Indexes    索引
    A good strategy for index selection is to match the predicates applied on the table access step. As
discussed in the earlier section, predicates on partitioning columns are pushed into the view and these
predicate are applied before executing the analytic functions. So, it’s probably a better approach to
index the partitioning columns if the SQL statements are using those predicates.
    对于索引选择一个好的策略是匹配应用于表访问步骤的谓词。正如之前讨论的，在分区列的谓词推进入了视图且这些谓词在执行分析函数之前应用。如此，如果SQL语句用到了分区列上的这些谓词，对它们建立索引可能是更佳的方法。
    In the Listing 8-23, a new index is added on the columns Country and Product. Step 4 in the
execution plan shows that index-based access is used. The predicate Information section shows that
predicates on all four partitioning columns were applied at step 4 and step 3 before executing analytic
function. But the predicate on Week column was applied much later in the execution plan at step 1. So,
in this case, adding Week column to the index is not useful as the predicates are not applied until after
the analytic function execution completes.
    列表8-23，对列Country和Product增加索引。在执行计划的第四步显示使用了基于索引的访问。谓词信息段显示，在所有四个分区列上的谓词都应用在执行分析函数的之前的第四步和第三步。但是在Week列上的谓词却在分析函数执行之后的第一步应用。如此，在这种情况下，在Week列上加索引就不是很有用，因为谓词直到分析函数执行完后才应用的。

Listing 8-23.  Predicates and Indices
create index sales_fact_i1 on  sales_fact( country, product);
 
1  select year, week, sale, max_weeks_5 from  max_5_weeks_vw
2    where country in ('Australia')  and product ='Xtend Memory' and
3    region='Australia' and year= 2000 and week <14
4*   order by year, week
/
------------------------------------------------------------------
| Id  | Operation                                      | Name                   | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                            |        |
|*  1|   VIEW                                         | MAX_5_WEEKS_VW  |      5 |
|   2 |     WINDOW SORT                          |                             |      5 |
|*  3 |       TABLE ACCESS BY INDEX ROWID| SALES_FACT           |      5 |
|*  4 |         INDEX RANGE SCAN                | SALES_FACT_I1      |    147 |
------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("WEEK"<14)
   3 - filter(("REGION"='Australia' AND "YEAR"=2000))
   4 - access("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory') fs