转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/10418869
正确答案: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可以物化查询结果集,从而避免多次执行一些非常耗占资源的数据库运算。在这里需要注意的是只是“可能”的独特性能优势。需要牢记于心的一点是物化结果集需要创建一个临时表并将数据行插入其中。如果同一个结果集将会被引用很多次的话,这样就可能是很值得的,否则就有可能极大地降低性能。
QQ:252803295
学习交流QQ群:
DSI&Core Search Ⅰ 群:127149411(技术:已满)
DSI&Core Search Ⅱ 群:177089463(技术:未满)
DSI&Core Search Ⅲ 群:284596437(技术:未满)
DSI&Core Search Ⅳ 群:192136702(技术:未满)
DSI&Core Search Ⅴ 群:285030382(闲聊:未满)
MAIL:oracledba_cn@hotmail.com
BLOG: http://blog.csdn.net/guoyjoe
WEIBO:http://weibo.com/guoyJoe0218
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM