《Pro Oracle SQL》CHAPTER 9 -
Performance Tuning with the Model Clause Model子句的性能调优 (page 293)As with all SQL, sometimes you need to tune statements using the Model clause. To that end, it helps to know how to read execution plans involving the clause. It also helps to know about some of the issues
you’ll encounter—such as predicate pushing and partitioning—when working with Model clause
queries.
如同所有SQL，有时你需要用Model子句调优语句。出于该的目的，知道如何读涉及该子句的执行计划是有帮助的。知道一些你会遇到的问题--如谓词推进和分区--当使用Model子句查询时，也是有帮助的。
Execution Plans 执行计划
In the Model clause, rule evaluation is the critical step. Rule evaluation can use one of five algorithm
types: ACYCLIC , ACYCLIC FAST, CYCLIC, ORDERED , and ORDERED FAST. The algorithm chosen depends upon the complexity and dependency of the rules themselves. The algorithm chosen also affects the performance of the SQL statement. But details of these algorithms are not well documented.
ACYCLIC FAST and ORDERED FAST algorithms are more optimized algorithms that allow cells to be
evaluated efficiently. However, the algorithm chosen depends upon the type of the rules that you
specify. For example, if there is a possibility of a cycle in the rules, then the algorithm that can handle
cyclic rules is chosen.
在Model子句中，规则求值是至关重要的步骤。规则求值会用到下列五种类型的算法：ACYCLIC , ACYCLIC FAST, CYCLIC, ORDERED , 和ORDERED FAST。但是这些算法的细节并没有文档可循。ACYCLIC FAST 和ORDERED FAST 算法是比较优化的算法，能让单元格有效率的求值。然而，算法的选择依赖于你所指定规则的类型。例如，如果有可能在规则中有循环，则将选择能处理循环的算法。
The algorithms of type ACYCLIC and CYCLIC are used if the SQL statement specifies the rules
automatic order clause. An ORDERED type of the rule evaluation algorithm is used if the SQL statement
specifies rules sequential order. If a rule is accessing individual cells without any aggregation, then
either the ACYCIC FAST or ORDERED FAST algorithm is used.
如果SQL语句指定rules automatic order子句则ACYCLIC和CYCLIC类型的算法会被选择。如果SQL语句指定rules sequential order则ORDERED 类型的规则求值算法会被选择。如果规则只是访问单个单元格而没有任何聚合，则要么ACYCIC FAST要么ORDERED FAST将被选择。
ACYCLIC
In Listing 9-23, a Model SQL statement and its execution plan is shown. Step 2 in the execution plan
shows that this SQL is using the SQL MODEL ACYCLIC algorithm for rule evaluation. The keyword ACYCLIC indicates that there are no possible CYCLIC dependencies between the rules. In this example, with the order by year, week clause you control the dependency between the rules, avoiding cycle
dependencies,
在列表9-23中，展示了一Model SQL语句和它的执行计划。在执行计划的步骤2中展示该SQL使用了SQL MODEL ACYCLIC算法对规则求值。关键字ACYCLIC指出这里不可能在规则间CYCLIC(循环的)依赖。在该例子中，用order by year, week 子句控制规则间的依赖性，避免循环依赖。
Listing 9-23. Automatic order and ACYCLIC
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( 0 inventory , sale, receipts)
8 rules automatic order(
9 inventory order by year, week =
10 nvl(inventory ,0)
11 - sale +
12 + receipts
13 )
14* order by product, country,year, week
---------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT ORDER BY | | 147 |
| 2 | SQL MODEL ACYCLIC | | 147 |
|* 3 | TABLE ACCESS FULL| SALES_FACT | 147 |
---------------------------------------------------
ACYCLIC FAST
If a rule is a simple rule accessing just one cell, the ACYCLIC FAST algorithm can be used. The execution plan in Listing 9-24 shows that the ACYCLIC FAST algorithm is used to evaluate the rule in this example.
如果某规则是简单的规则只是访问一个单元格，就能选择ACYCLIC FAST算法。在列表9-24的执行计划显示在本例中ACYCLIC FAST算法用于规则求值。
Listing 9-24. Automatic Order and ACYCLIC FAST
1 select distinct product, country, year,week, sale_first_Week
2 from sales_fact
3 where country in ('Australia') and product='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year,week)
7 measures ( 0 sale_first_week ,sale )
8 rules automatic order(
9 sale_first_week = 0.12*sale
10 )
11* order by product, country,year, week
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | SQL MODEL ACYCLIC FAST | |
|* 3 | TABLE ACCESS FULL | SALES_FACT |
----------------------------------------------
CYCLIC
The execution plan in Listing 9-25 shows the use of CYCLIC algorithm to evaluate the rules. The SQL in Listing 9-25 is the copy of Listing 9-23 except for that the clause order by year, week is removed from
the rule in line 9. Without the order-by clause, row evaluation can happen in any order, and so the
CYCLIC algorithm is chosen.
在列表9-25的执行计划展示使用CYCLIC算法规则求值。在列表9-25中的SQL是列表9-23的copy除了第9行的rule中去除了子句order by year, week。没有了order-by子句，行的求值可以以任意顺序发生，因此CYCLIC算法被选择。
Listing 9-25. Automatic Order and CYCLIC
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( 0 inventory , sale, receipts)
8 rules automatic order(
9 inventory /*order by year, week*/ =
10 nvl(inventory ,0)
11 - sale +
12 + receipts
13 )
14* order by product, country,year, week
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | SQL MODEL CYCLIC | |
|* 3 | TABLE ACCESS FULL| SALES_FACT |
------------------------------------------
Sequential
If the rule specifies sequential order, then the evaluation algorithm of the rules is shown as ORDERED.
Listing 9-26 shows an example.
如果规则指定sequential order，则规则求值算法显示的是ORDERER。列表9-26展示了一个例子。
Listing 9-26. Sequential Order
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( 0 inventory , sale, receipts)
8 rules sequential order(
9 inventory order by year, week =
10 nvl(inventory ,0)
11 - sale +
12 + receipts
13 )
14* order by product, country,year, week
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | SQL MODEL ORDERED | |
|* 3 | TABLE ACCESS FULL| SALES_FACT |
-------------------------------------------
In a nutshell, the complexity and inter-dependency of the rules plays a critical role in the
algorithm chosen. ACYCLIC FAST and ORDERED FAST algorithms are more scalable. This becomes
important as the amount of data increases.
页:
[1]