《Pro Oracle SQL》Chapter8--8.9 Advanced topics

Advanced topics     高级主题      (page 268)
    A few advanced topics about the analytic functions are worthy of discussion. I will discuss topics such as dynamic analytic statements, nesting of analytic functions, parallelism, and PGA size.
    关于分析函数的一些高级主体很值得讨论。我将讨论如动态分析语句，分析函数的嵌套，并行性 ，以及PGA大小。
Dynamic SQL     动态SQL
    A common question about the analytic SQL statement is whether a bind variable can be used in place of partitioning or sorting columns. No. If you want the flexibility to modify the partitioning or sorting columns dynamically, you need to use dynamic SQL statements. Static analytic SQL statements can not change the partitioning or sorting columns.
    分析函数有一个常见的问题是：绑定变量是否能用于替换分区或排序列么？答案是不能。 如果你需要动态修改分区列和排序列(的这种弹性),你就需要使用动态SQL语句。 静态分析SQL语句不能改变分区或排序列。
    If your goal is to modify the partitioning columns dynamically, then consider creating a packaged procedure to capture the logic in the procedure. In the Listing 8-24, the procedure Analytic_dynamic_prc accepts a string to be used as partitioning columns. A SQL statement is constructed using the arguments passed and executed dynamically using Execute immediate  syntax. Result of the analytic statement is fetched into an array and printed using a call to dbms_output package. 
    如果你的目标是动态的修改分区列，就要考虑创建打包的存储过程，把逻辑封装在存储过程中。在列表8-24中，存储过程 Analytic_dynamic_prc接收一字符串用做分区列。SQL语句使用传入的参数构造且使用Execute immediate句法动态的执行。分析语句的结果从数组中取出且调用dbms_output包打印出结果。
    In the first call, the analytic_dynamic_prc passes the string  product, country, region  as the first argument and the columns in this list are used as the partitioning columns. The second call to the procedure uses the string  product, country, region, year  to use a different list of columns for the partitioning-clause.
    在第一次调用中，analytic_dynamic_prc传递字符串“product, country, region”作为第一个参数，在列表中的列用做分区列。第二次调用过程使用字符串“product, country, region, year”作为分区子句的不同的列表。
    Note that this procedure is given as an example and as such may not be construed as a production-ready code.
    注意这个过程仅作为一个例子，可能用于生产环境的代码不能如此构造。
Listing 8-24.  Dynamic SQL Statement
create or replace procedure  
  analytic_dynamic_prc ( part_col_string varchar2, v_country varchar2, v_product varchar2)
is 
  type numtab is table of number(18,2) index by binary_integer;
  l_year numtab;
  l_week numtab;
  l_sale numtab;
  l_rank numtab;
  l_sql_string  varchar2(512) ;
begin
 l_sql_String := 
 'select * from (
   select  year, week,sale, 
    rank() over(
          partition by ' ||part_col_string ||'
          order by sale desc
           ) sales_rank
   from sales_fact
   where country in (' ||chr(39) || v_country || chr(39) || ' )  and 
         product =' || chr(39) || v_product || chr(39) ||
       ' order by product, country,year, week
    ) where sales_rank<=10
    order by 1,4';
 execute immediate l_sql_string bulk collect into  l_year, l_week, l_sale, l_rank;
 for  i  in 1 .. l_year.count
  loop
       dbms_output.put_line ( l_year(i) ||' |' || l_week (i) ||
                            '|'|| l_sale(i) || '|' || l_rank(i) ); 
  end loop;
 end;
/
 
exec analytic_dynamic_prc ( 'product, country, region','Australia','Xtend Memory');
...
1998 |48|172.56|9
2000 |46|246.74|3
2000 |21|187.48|5
2000 |43|179.12|7
2000 |34|178.52|8
2001 |16|278.44|1
2001 |4|256.7|2
exec analytic_dynamic_prc ( 'product, country,region, year','Australia','Xtend Memory');
 
1998 |48|172.56|1
1998 |10|117.76|2
1998 |18|117.56|3
1998 |23|117.56|3
1998 |26|117.56|3
1998 |38|115.84|6
1998 |42|115.84|6
...

Nesting Analytic Functions
    Analytic functions can not be nested, but a nesting effect can be achieved with the use of subqueries. For example, the clause lag(first_value(column,1),1) is syntactically incorrect. Subqueries can be used to
create a nesting effect, as you’ll see below.
    分析函数不能嵌套，但是使用子查询能取得嵌套效果。 例如，子句lag(first_value(column,1),1)句法上是错误的。你将看到，使用子查询能创建嵌套效果。
    Suppose your goal is to fetch the maximum Sale column value for the year and the prior year in the same row; if so, then analytic functions lag  and first_value  can be used in the subqueries to write a SQL statement. In Listing 8-25, inner subquery is fetching the Year and Week Sale column value in which the maximum sale occurred, in addition to fetching the maximum Sale column value for that year. The lag function in the outer query retrieves the prior Year Maximum Sale column value.
    假设你的目标是在同一行中取出当年和上一年的最大的Sale列值。在列表8-25中，内部子查询取出的是最大sale值所在行的Year，Week列值，还有取出那年的最大Sale列值。外部查询的lag函数检索上一年最大Sale列值。
    Notice that the partitioning clause is different between lag and  first_value  functions. Analytic function first_value  is computing the top Sale row in a partition specified by the partitioning columns product, country, region, year  whereas the lag is fetching the first row from the prior year specifying only sorting-clause:  order by year desc . 
    注意lag和first_value函数的分区子句是有区别的。分析函数first_value计算的是按分区列product, country, region, year指定分区的第一Sale列值所在行，而lag则取的是由排序子句“order by year desc”指定的前一年的第一行。
    With multi-level nesting of analytic functions, complex goals can be implemented concisely using the analytic functions.
    通过多层分析函数嵌套，复杂的目标就能通过精简的使用分析函数得以执行。
Listing 8-25. Nesting Analytic Functions
select  year, week, top_sale_year,
    lag( top_sale_year) over ( order by year desc)  prev_top_sale_yer
from (
 select distinct
    first_value ( year)  over ( 
          partition by product, country, region ,year
          order by sale desc
          rows between unbounded preceding and unbounded following
     ) year,
    first_value ( week) over ( 
          partition by product, country, region ,year
          order by sale desc
          rows between unbounded preceding and unbounded following
     ) week,  
    first_value (sale) over(
          partition by product, country, region ,year
          order by sale desc
          rows between unbounded preceding and unbounded following
     ) top_sale_year
  from sales_fact
  where country in ('Australia')  and product ='Xtend Memory'
)
  order by year, week
/
 
 YEAR     WEEK     TOP_SALE_YEAR     PREV_TOP_SALE_YER
-----          ----           -------------                 -----------------
 1998       48            172.56                      148.12
 1999       17            148.12                      246.74
 2000       46             246.74                     278.44
 2001       16             278.44

Parallelism     并行性
    By specifying a parallel hint in the SQL statement or by setting parallelism at the object level, analytic functions can be parallelized. If you have huge amount of data that needs to be processed using analytic functions, parallelism is a good choice. A SQL statement using multi-level nesting also can benefit from parallelism. 
    通过在SQL语句中指定并发提示或者通过在对象层级设定并行性，分析函数就能并发化(执行)。 如果你有大量的数据需要使用分析函数处理，并行性就是不错的选择。使用了多层嵌套的SQL语句也能从并行性中获益。
    Listing 8-26 shows the execution plan for the query in the Listing 8-25 using parallelism. In the execution plan, there are two WINDOW operations as the SQL statement has nested the lag and first_value analytic functions.
    列表8-26展示了列表8-25查询使用并行性后的执行计划。在执行计划中，有两处WINDOW操作，因为SQL语句嵌套了分析函数lag和first_value。
    Optimal distribution of rows between the PQ slaves is critical to maintain functional correctness and that is automatically handled by Oracle database. 
    在 PQ副盘(Parallel Query slaves) 之间行集的优化分布对于保持功能正确性是至关重要的，且是由Oracle数据库自动处理的。
Listing 8-26. Parallelism
----------------------------------------------------
 Id  | Operation                                                          | Name
-----------------------------------------------------
   0 | SELECT STATEMENT                                        |
   1 |   SORT ORDER BY                                            |
   2 |     WINDOW BUFFER                                         |
   3 |        PX COORDINATOR                                     |
   4  |          PX SEND QC(ORDER)                               |  : T Q1 0 0 03
   5  |            SORT ORDER BY                                  |
   6  |              PX  RECEIVE                                     |
   7  |                PX SEND RANGE                               |  : T Q1 0 0 02
   8  |                  VIEW                                             |
   9  |                   HASH UNIQUE                               |
   10  |                     PX RECEIVE                              |
   11  |                        PX SEND HASH                       |  : T Q1 0 0 01
   12  |                          WINDOW SORT                      |
   13  |                             PX RECEIVE                      |
   14  |                               PX SEND HASH                |  : T Q1 0 0 00
   15  |                                 PX  BLOCK  ITERATOR     |
*  16  |                                   TABLE ACCESS FULL    |  SALES_FACT

PGA size
    Most operations associated with the analytic functions are performed in the Program Global Area(PGA) of the process. So, for optimal performance it is important to have a big enough memory area so that programs can execute analytic functions without spilling to the disk. This is very analogous to a Sort operation. If the Sort operation spills to the disk due to a lower value of the memory size, then the performance of the Sort operation will not be optimal.Similarly, the execution performance of analytic functions will suffer if the operation spills to the disk.

    与分析函数有关联的大部分操作在程序全局区(PGA)中执行。 如此以来，对优化性能而言有充足的内存区域将使得程序能执行分析函数而不要溢出 到磁盘中。这同Sort操作如出一辙。如果Sort操作由于低容量的内存溢出到磁盘，则Sort操作将不是优化的了。相似的，如果Sort操作溢出到磁盘则分析函数的执行性能将大打折扣。
    Database initialization parameter PGA_AGGREGATE_TARGET(PGAT) controls the cumulative maximum size of the PGA. By default, a serial process can allocate a PGA up to the maximum size of 5% of PGAT value. For parallel processes, the limit is up to 30% of PGAT. It is essential to keep PGAT to a bigger value to improve the performance of analytic functions.
    数据库初始化参数 PGA_AGGREGATE_TARGET(PGAT)控制着累积的最大PGA尺寸。默认情况下，一串行进程能最多分配5%的PGAT容量的PGA。而对于并行进程，这个限制上升到30%。保持PGAT足够的大对提高分析函数的性能有实质意义。
Organizational Behavior
   The hardest thing about analytic function is the organizational resistance to change. Developers and databae administrators are comfortable writing SQL statements using conventional syntax. Using analytic syntax will not come easy. However, these developers and database administrators need to embrace the change. Another plus: use of analytic functions forces one to think in terms of sets.

    关于分析函数最难的事是团队对(新事物)改变的阻力。开发者和数据库管理员满足于是用传统句法写SQL。推行分析函数将不那么顺利。然而，这些开发者和数据库管理员需要拥抱这种改变。另一个好处：使用分析函数迫使人们依照集合的方式来思考。
    Oracel Corporation releases new features in every major release of Oracle Database. We need to harness the new features to write more efficient and concise SQL statements. Proper training for these new features is also essential and hopefully, this chapter provided an insight in to analytic functions.

    Oracle公司在每一个Oracle数据库主要版本发布新特性。我们需要驾驭这些新特性来写出更加有效，精简的SQL语句。对这些新特性适当的培训也是必要的和有前途的，本章深入讨论了分析函数。
    When you start writing SQL statement utilizing the analytic functions, start with simpler SQL statement. Then add more complexity to meet tht goal.
    当你开始利用分析函数写SQL语句，开始用简单的SQL语句，然后逐渐的复杂，最终达到目的。
Summary      总结
    Complex SQL statements can be written using analytic functions concisely. Understanding analytic function provides you whole new way of thinking, analytically speaking. The ability to reference another row combined with partitioning and windowing clase allows you to simplify complex SQL statements. Many performance issues can be resolved rewriting the SQL statement using analytic functions, and that resistance can be easily overcome by showing the performance improvements with analytic functions.

    复杂的SQL语句能使用分析函数简明的写出来。理解分析函数给予你全新的方式思考，分析的表述。 行间引用的能力结合分区和窗口子句使得你能够简化复杂的SQL语句。许多性能问题通过使用分析函数重写SQL语句迎刃而解，且通过展示分析函数所带来的性能提升(推广它的)阻力也将容易克服。