[每日一题] OCP1z0-047 :2013-08-27 WITH子句.........................................................121

正确答案：CD

 题目解析：
 没有进行子查询因子化的交叉数据分析查询

gyj@OCM>        select /*+ gather_plan_statistics */
  2             product
  3             , channel
  4             , quarter
  5             , country
  6             , quantity_sold
  7     from
  8     (
  9             select
 10                     prod_name product
 11                     , country_name country
 12                     , channel_id channel
 13                     , substr(calendar_quarter_desc, 6,2) quarter
 14                     , sum(amount_sold) amount_sold
 15                     , sum(quantity_sold) quantity_sold
 16             from
 17                     sh.sales
 18                     join sh.times on times.time_id = sales.time_id
 19                     join sh.customers on customers.cust_id = sales.cust_id
 20                     join sh.countries on countries.country_id = customers.country_id
 21                     join sh.products on products.prod_id = sales.prod_id
 22             group by
 23               prod_name
 24               , country_name
 25               , channel_id
 26               , substr(calendar_quarter_desc, 6, 2)
 27     )
 28  ) PIVOT (
 29     sum(quantity_sold)
 30     FOR (channel, quarter) IN
 31     (
 32             (5, '02') AS CATALOG_Q2,
 33             (4, '01') AS INTERNET_Q1,
 34             (4, '04') AS INTERNET_Q4,
 35             (2, '02') AS PARTNERS_Q2,
 36             (9, '03') AS TELE_Q3
 37     )
 38  )
 39  order by product, country;

  现在让我们使用with子句来将这个查询分解为易于理解的字节级大小的块。
   进行子查询因子化的交叉表

 gyj@OCM> with sales_countries as (
  2     select /*+ gather_plan_statistics */
  3             cu.cust_id
  4             , co.country_name
  5     from    sh.countries co, sh.customers cu
  6     where cu.country_id = co.country_id
  7  ),
  8  top_sales as (
  9     select 
 10             p.prod_name 
 11             , sc.country_name
 12             , s.channel_id
 13             , t.calendar_quarter_desc
 14             , s.amount_sold
 15             , s.quantity_sold
 16     from
 17             sh.sales s
 18             join sh.times t on t.time_id = s.time_id
 19             join sh.customers c on c.cust_id = s.cust_id
 20             join sales_countries sc on sc.cust_id = c.cust_id
 21             join sh.products p on p.prod_id = s.prod_id
 22  ),
 23  sales_rpt as (
 24     select
 25             prod_name product
 26             , country_name country
 27             , channel_id channel
 28             , substr(calendar_quarter_desc, 6,2) quarter
 29             , sum(amount_sold) amount_sold
 30             , sum(quantity_sold) quantity_sold 
 31     from top_sales
 32     group by 
 33             prod_name
 34             , country_name
 35             , channel_id
 36             , substr(calendar_quarter_desc, 6, 2)
 37  )
 38  select * from
 39  ( 
 40    select product, channel, quarter, country, quantity_sold
 41    from sales_rpt
 42  ) pivot (
 43     sum(quantity_sold)
 44     for (channel, quarter) in
 45     (
 46             (5, '02') as catalog_q2,
 47             (4, '01') as internet_q1,
 48             (4, '04') as internet_q4,
 49             (2, '02') as partners_q2,
 50             (9, '03') as tele_q3
 51     )
 52  )
 53  order by product, country；

尽管这并不是一个非常复杂的SQL例子，但确实可以用来说明WITH子句是如何能够被用来增强SQL语句的可读性和可维护性的，通过使用这一技术，大而复杂的查询可以变得更易于理解。
 
    WITH query_name子句可以让你为子查询块分配一个名称。然后你就可以通过声明query_name在查询中多次引用这个子查询。Oracle数据库通过将这个查询名称作为内嵌视图或临时表对等来优化查询。

    注意Oracle可能将因子化的子查询作为临时表来处理。在一个表被引用多次的查询中，这可能是一个独特的性能上的优势，因为Oracle可以物化查询结果集，从而避免多次执行一些非常耗占资源的数据库运算。在这里需要注意的是只是“可能”的独特性能优势。需要牢记于心的一点是物化结果集需要创建一个临时表并将数据