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

正确答案：CD

题目解析：

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

[html] 
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子句来将这个查询分解为易于理解的字节级大小的块。

进行子查询因子化的交叉表

[html] 
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可以物化查询结果集，从而避免多次执行一些非常耗占资源的数据库运算。在这里需要注意的是只是“可能”的独特性能优势。需要牢记于心的一点是物化结果集需要创建一个临时表并将数据行插入其中。如果同一个结果集将会被引用很多次的话，这样就可能是很值得的，否则就有可能极大地降低性能。