Oracle SQL高级编程——子查询因子化全解析
来源：互联网 发布：sha1算法的c语言实现 编辑：程序博客网 时间：2024/05/16 05:35
参见《Oracle SQL高级编程》。
概述
子查询因子化就是ANSI中的公共表达式。
从11.2开始，子查询因子化开始支持递归。可以实现CONNECT BY的功能。
标准的子查询因子化的例子
这是一个非常复杂的查询，下面是不加因子化的版本。注意PIVOT的用法。
select * from ( select /*+ gather_plan_statistics */ product , channel , quarter , country , quantity_sold from ( select prod_name product , country_name country , channel_id channel , substr(calendar_quarter_desc , 6 , 2 ) quarter , sum(amount_sold) amount_sold , sum(quantity_sold) quantity_sold from sh.sales join sh.times on times.time_id = sales.time_id join sh.customers on customers.cust_id = sales.cust_id join sh.countries on countries.country_id = customers.country_id join sh.products on products.prod_id = sales.prod_id group by prod_name , country_name , channel_id , substr(calendar_quarter_desc , 6 , 2 ) ))pivot( sum(quantity_sold) for(channel , quarter ) in ( (5 , '02' ) as catalog_q2 , (4 , '01' ) as internet_q1 , (4 , '04' ) as internet_q4 , (2 , '02' ) as partners_q2 , (9 , '03' ) as tele_q3 ))order by product , country ;执行结果如下所示（节选）PRODUCT COUNTRY CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2 TELE_Q3------------------------------------------------------------ ---------------------------------------- ---------- ----------- ----------- ----------- ----------Model C9827B Cordless Phone Battery Spain 6 9 25Model C9827B Cordless Phone Battery TurkeyModel C9827B Cordless Phone Battery United Kingdom 17 23 45Model C9827B Cordless Phone Battery United States of America 151 310 522Model CD13272 Tricolor Ink Cartridge ArgentinaModel CD13272 Tricolor Ink Cartridge Australia 16 17 39Model CD13272 Tricolor Ink Cartridge BrazilModel CD13272 Tricolor Ink Cartridge Canada 12 20 26Model CD13272 Tricolor Ink Cartridge Denmark 10 15 19Model CD13272 Tricolor Ink Cartridge France 15 14 27Model CD13272 Tricolor Ink Cartridge Germany 28 35 64Model CD13272 Tricolor Ink Cartridge Italy 27 23 45Model CD13272 Tricolor Ink Cartridge Japan 24 31 73Model CD13272 Tricolor Ink Cartridge Singapore 13 20 33Model CD13272 Tricolor Ink Cartridge Spain 11 8 17Model CD13272 Tricolor Ink Cartridge TurkeyModel CD13272 Tricolor Ink Cartridge United Kingdom 16 30 53Model CD13272 Tricolor Ink Cartridge United States of America 244 314 629Model K3822L Cordless Phone Battery ArgentinaModel K3822L Cordless Phone Battery Australia 19 21 49子查询因子化的写法，共有三个因子，而且相互之间有关联
with sales_countries as ( select /*+ gather_plan_statistics */ cu.cust_id , co.country_name from sh.countries co , sh.customers cu where cu.country_id = co.country_id ) ,top_sales as( select p.prod_name , sc.country_name , s.channel_id , t.calendar_quarter_desc , s.amount_sold , s.quantity_sold from sh.sales s join sh.times t on t.time_id = s.time_id join sh.customers c on c.cust_id = s.cust_id join sales_countries sc on sc.cust_id = c.cust_id join sh.products p on p.prod_id = s.prod_id ) ,sales_rpt as ( select prod_name product , country_name country , channel_id channel , substr(calendar_quarter_desc , 6 , 2 ) quarter , sum(amount_sold) amount_sold , sum(quantity_sold) quantity_sold from top_sales group by prod_name , country_name , channel_id , substr(calendar_quarter_desc , 6 , 2 ))select * from ( select product , channel , quarter , country , quantity_sold from sales_rpt)pivot( sum(quantity_sold) for(channel , quarter ) in ( (5 , '02' ) as catalog_q2 , (4 , '01' ) as internet_q1 , (4 , '04' ) as internet_q4 , (2 , '02' ) as partners_q2 , (9 , '03' ) as tele_q3 ))order by product , country ;子查询因子化所带来的好处之一
如果一个因子被多处引用，那么Oracle就会为这个因子建立临时表，免得每次都要执行。但是如果选择的不恰当，也可以极大的降低性能。
对于查询因子采用临时表的控制及各自的执行计划
用提示将查询因子物化成临时表。（不加提示时，本例也会默认采用这种办法）
explain plan for with cust as( select /*+ materialize gather_plan_statistics */ b.cust_income_level , a.country_name from sh.customers b join sh.countries a on a.country_id = b.country_id )select country_name , cust_income_level , count(country_name) country_cust_count from cust chaving count(country_name) > (select count(*)*.1 from cust c2 ) or count(cust_income_level) >= ( select median(income_level_count) from ( select cust_income_level , count(*)*.25 income_level_count from cust group by cust_income_level ) )group by country_name , cust_income_level order by 1 , 2 ;SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 3111068495--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 620 | 495 (1)| 00:00:06 || 1 | TEMP TABLE TRANSFORMATION | | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6607_1A61BF | | | | ||* 3 | HASH JOIN | | 55500 | 2167K| 409 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 ||* 6 | FILTER | | | | | || 7 | SORT GROUP BY | | 20 | 620 | 87 (4)| 00:00:02 || 8 | VIEW | | 55500 | 1680K| 84 (0)| 00:00:02 || 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 || 10 | SORT AGGREGATE | | 1 | | | || 11 | VIEW | | 55500 | | 84 (0)| 00:00:02 || 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 || 13 | SORT GROUP BY | | 1 | 13 | | || 14 | VIEW | | 12 | 156 | 87 (4)| 00:00:02 || 15 | SORT GROUP BY | | 12 | 252 | 87 (4)| 00:00:02 || 16 | VIEW | | 55500 | 1138K| 84 (0)| 00:00:02 || 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------ 3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*.1 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_1A61BF" "T1") "C2") OR COUNT("CUST_INCOME_LEVEL")>= (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*)*.25 "INCOME_LEVEL_COUNT" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL") "from$_subquery$_006"))36 rows selected.使用INLINE提示，查询因子做内联处理。
explain plan for with cust as( select /*+ inline gather_plan_statistics */ b.cust_income_level , a.country_name from sh.customers b join sh.countries a on a.country_id = b.country_id )select country_name , cust_income_level , count(country_name) country_cust_count from cust chaving count(country_name) > (select count(*)*.1 from cust c2 ) or count(cust_income_level) >= ( select median(income_level_count) from ( select cust_income_level , count(*)*.25 income_level_count from cust group by cust_income_level ) )group by country_name , cust_income_level order by 1 , 2 ;SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 33565775------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 800 | 411 (1)| 00:00:05 ||* 1 | FILTER | | | | | || 2 | SORT GROUP BY | | 20 | 800 | 411 (1)| 00:00:05 ||* 3 | HASH JOIN | | 55500 | 2167K| 409 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 || 6 | SORT AGGREGATE | | 1 | 9 | | ||* 7 | HASH JOIN | | 55500 | 487K| 37 (3)| 00:00:01 || 8 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 || 9 | INDEX FAST FULL SCAN| CUST_COUNTRYID | 55500 | 216K| 35 (0)| 00:00:01 || 10 | SORT GROUP BY | | 1 | 13 | | || 11 | VIEW | | 12 | 156 | 409 (1)| 00:00:05 || 12 | SORT GROUP BY | | 12 | 360 | 409 (1)| 00:00:05 ||* 13 | HASH JOIN | | 55500 | 1625K| 407 (1)| 00:00:05 || 14 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 || 15 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(COUNT(*)> (SELECT COUNT(*)*.1 FROM "SH"."COUNTRIES"PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------ "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID") OR COUNT("B"."CUST_INCOME_LEVEL")>= (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "B"."CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*)*.25 "INCOME_LEVEL_COUNT" FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL") "from$_subquery$_006")) 3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 7 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 13 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")36 rows selected.注意在这个例子中，内联处理要快过临时表的方法。
使用临时表的性能强过内联的例子
先清空两池。SYS@ prod> alter system flush buffer_cache ;System altered.Elapsed: 00:00:00.22SYS@ prod> alter system flush shared_pool ;System altered.with cust as( select /*+ inline gather_plan_statistics */ b.cust_income_level , a.country_name from sh.customers b join sh.countries a on a.country_id = b.country_id ) ,median_income_set as ( select /*+ inline */ cust_income_level , count(*) income_level_count from cust group by cust_income_level having count(cust_income_level) > ( select median(income_level_count) income_level_count from ( select cust_income_level , count(*) income_level_count from cust group by cust_income_level ) ))select country_name , cust_income_level , count(country_name) country_cust_count from cust chaving count(country_name) > (select count(*)*.1 from cust c2 ) or cust_income_level in ( select mis.cust_income_level from median_income_set mis )group by country_name , cust_income_level ;SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 1450169399------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 800 | 411 (1)| 00:00:05 ||* 1 | FILTER | | | | | || 2 | HASH GROUP BY | | 20 | 800 | 411 (1)| 00:00:05 ||* 3 | HASH JOIN | | 55500 | 2167K| 409 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 || 6 | SORT AGGREGATE | | 1 | 9 | | ||* 7 | HASH JOIN | | 55500 | 487K| 37 (3)| 00:00:01 || 8 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 || 9 | INDEX FAST FULL SCAN| CUST_COUNTRYID | 55500 | 216K| 35 (0)| 00:00:01 ||* 10 | FILTER | | | | | || 11 | HASH GROUP BY | | 1 | 30 | 409 (1)| 00:00:05 ||* 12 | HASH JOIN | | 55500 | 1625K| 407 (1)| 00:00:05 || 13 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 || 14 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 || 15 | SORT GROUP BY | | 1 | 13 | | || 16 | VIEW | | 12 | 156 | 409 (1)| 00:00:05 || 17 | SORT GROUP BY | | 12 | 360 | 409 (1)| 00:00:05 ||* 18 | HASH JOIN | | 55500 | 1625K| 407 (1)| 00:00:05 || 19 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 || 20 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 |------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(COUNT(*)> (SELECT COUNT(*)*.1 FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID") OR EXISTS (SELECT 0 FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL" HAVING "B"."CUST_INCOME_LEVEL"=:B1 AND COUNT("B"."CUST_INCOME_LEVEL")> (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "B"."CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL") "from$_subquery$_005"))) 3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 7 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 10 - filter("B"."CUST_INCOME_LEVEL"=:B1 AND COUNT("B"."CUST_INCOME_LEVEL")> (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "B"."CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM "SH"."COUNTRIES" "A","SH"."CUSTOMERS" "B" WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID" GROUP BY "B"."CUST_INCOME_LEVEL") "from$_subquery$_005")) 12 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 18 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")执行结果COUNTRY_NAME CUST_INCOME_LEVEL COUNTRY_CUST_COUNT---------------------------------------- ------------------------------ ------------------China F: 110,000 - 129,999 181Poland H: 150,000 - 169,999 61Singapore H: 150,000 - 169,999 50New Zealand H: 150,000 - 169,999 21Brazil E: 90,000 - 109,999 105Denmark E: 90,000 - 109,999 61114 rows selected.Elapsed: 00:00:00.51使用临时表。with cust as( select /*+ materialize gather_plan_statistics */ b.cust_income_level , a.country_name from sh.customers b join sh.countries a on a.country_id = b.country_id ) ,median_income_set as ( select /*+ inline */ cust_income_level , count(*) income_level_count from cust group by cust_income_level having count(cust_income_level) > ( select median(income_level_count) income_level_count from ( select cust_income_level , count(*) income_level_count from cust group by cust_income_level ) ))select country_name , cust_income_level , count(country_name) country_cust_count from cust chaving count(country_name) > (select count(*)*.1 from cust c2 ) or cust_income_level in ( select mis.cust_income_level from median_income_set mis )group by country_name , cust_income_level ;SH@ prod> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 663917268--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 620 | 495 (1)| 00:00:06 || 1 | TEMP TABLE TRANSFORMATION | | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660D_1A61BF | | | | ||* 3 | HASH JOIN | | 55500 | 2167K| 409 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1354K| 405 (1)| 00:00:05 ||* 6 | FILTER | | | | | || 7 | HASH GROUP BY | | 20 | 620 | 87 (4)| 00:00:02 || 8 | VIEW | | 55500 | 1680K| 84 (0)| 00:00:02 || 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 || 10 | SORT AGGREGATE | | 1 | | | || 11 | VIEW | | 55500 | | 84 (0)| 00:00:02 || 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 ||* 13 | FILTER | | | | | || 14 | HASH GROUP BY | | 1 | 21 | 87 (4)| 00:00:02 || 15 | VIEW | | 55500 | 1138K| 84 (0)| 00:00:02 || 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 || 17 | SORT GROUP BY | | 1 | 13 | | || 18 | VIEW | | 12 | 156 | 87 (4)| 00:00:02 || 19 | SORT GROUP BY | | 12 | 252 | 87 (4)| 00:00:02 || 20 | VIEW | | 55500 | 1138K| 84 (0)| 00:00:02 || 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1A61BF | 55500 | 1680K| 84 (0)| 00:00:02 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID") 6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*.1 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "C2") OR EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL" HAVING "CUST_INCOME_LEVEL"=:B1 AND COUNT("CUST_INCOME_LEVEL")> (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL") "from$_subquery$_005"))) 13 - filter("CUST_INCOME_LEVEL"=:B1 AND COUNT("CUST_INCOME_LEVEL")> (SELECT PERCENTILE_CONT(0.500000) WITHIN GROUP ( ORDER BY "INCOME_LEVEL_COUNT") FROM (SELECT "CUST_INCOME_LEVEL" "CUST_INCOME_LEVEL",COUNT(*) "INCOME_LEVEL_COUNT" FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D660D_1A61BF" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL") "from$_subquery$_005"))49 rows selected.COUNTRY_NAME CUST_INCOME_LEVEL COUNTRY_CUST_COUNT---------------------------------------- ------------------------------ ------------------China F: 110,000 - 129,999 181Poland H: 150,000 - 169,999 61Singapore H: 150,000 - 169,999 50New Zealand H: 150,000 - 169,999 21Brazil E: 90,000 - 109,999 105Denmark E: 90,000 - 109,999 61114 rows selected.Elapsed: 00:00:00.32用因子化优化SQL（*）
存在这样一条老SQL
select /*+ gather_plan_statistics */ substr(prod_name , 1 , 30 ) prod_name , channel_desc , ( select avg(c2.unit_cost) from sh.costs c2 where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' ) and to_date('12/31/2000' , 'mm/dd/yyyy') ) avg_cost , ( select min(c2.unit_cost) from sh.costs c2 where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' ) and to_date('12/31/2000' , 'mm/dd/yyyy') ) min_cost , ( select max(c2.unit_cost) from sh.costs c2 where c2.prod_id = c.prod_id and c2.channel_id = c.channel_id and c2.time_id between to_date('01/01/2000' , 'mm/dd/yyyy' ) and to_date('12/31/2000' , 'mm/dd/yyyy') ) max_cost from ( select distinct pr.prod_id , pr.prod_name , ch.channel_id , ch.channel_desc from sh.channels ch , sh.products pr , sh.costs co where ch.channel_id = co.channel_id and co.prod_id = pr.prod_id and co.time_id between to_date('01/01/2000' , 'mm/dd/yyyy') and to_date('12/31/2000' , 'mm/dd/yyyy')) corder by prod_name , channel_desc ;执行时间Elapsed: 00:00:00.36执行计划SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1877279774------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20640 | 1310K| | 638 (1)| 00:00:08 | | || 1 | SORT AGGREGATE | | 1 | 20 | | | | | || 2 | PARTITION RANGE ITERATOR | | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 ||* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 || 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | ||* 5 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 || 6 | SORT AGGREGATE | | 1 | 20 | | | | | || 7 | PARTITION RANGE ITERATOR | | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 ||* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 || 9 | BITMAP CONVERSION TO ROWIDS | | | | | | | | ||* 10 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 || 11 | SORT AGGREGATE | | 1 | 20 | | | | | || 12 | PARTITION RANGE ITERATOR | | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 | 1920 | | 17 (0)| 00:00:01 | 13 | 16 || 14 | BITMAP CONVERSION TO ROWIDS | | | | | | | | ||* 15 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 || 16 | SORT ORDER BY | | 20640 | 1310K| 1632K| 638 (1)| 00:00:08 | | || 17 | VIEW | | 20640 | 1310K| | 315 (1)| 00:00:04 | | || 18 | HASH UNIQUE | | 20640 | 1169K| 1384K| 315 (1)| 00:00:04 | | ||* 19 | HASH JOIN | | 20640 | 1169K| | 24 (5)| 00:00:01 | | || 20 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | | 3 (0)| 00:00:01 | | ||* 21 | HASH JOIN | | 20640 | 564K| | 21 (5)| 00:00:01 | | |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 22 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 3 (0)| 00:00:01 | | || 23 | PARTITION RANGE ITERATOR | | 20640 | 302K| | 17 (0)| 00:00:01 | 13 | 16 ||* 24 | TABLE ACCESS FULL | COSTS | 20640 | 302K| | 17 (0)| 00:00:01 | 13 | 16 |------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 5 - access("C2"."PROD_ID"=:B1) 8 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 10 - access("C2"."PROD_ID"=:B1) 13 - filter("C2"."CHANNEL_ID"=:B1 AND "C2"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 15 - access("C2"."PROD_ID"=:B1) 19 - access("CO"."PROD_ID"="PR"."PROD_ID") 21 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID") 24 - filter("CO"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))44 rows selected.用使WITH子句进行重构
with bookends as ( select to_date('01/01/2000' , 'mm/dd/yyyy' ) begin_date , to_date('12/31/2000' , 'mm/dd/yyyy') end_date from dual ) ,prodmaster as ( select distinct pr.prod_id , pr.prod_name , ch.channel_id , ch.channel_desc from sh.channels ch , sh.products pr , sh.costs co where ch.channel_id = co.channel_id and co.prod_id = pr.prod_id and co.time_id between (select begin_date from bookends) and (select end_date from bookends)) ,cost_compare as ( select prod_id , channel_id , avg(c2.unit_cost) avg_cost , min(c2.unit_cost) min_cost , max(c2.unit_cost) max_cost from sh.costs c2 where c2.time_id between ( select begin_date from bookends ) and ( select end_date from bookends ) group by c2.prod_id , c2.channel_id )select /*+ gather_plan_statistics */ substr(pm.prod_name , 1 , 30) prod_name , pm.channel_desc , cc.avg_cost , cc.min_cost , cc.max_cost from prodmaster pmjoin cost_compare cc on cc.prod_id = pm.prod_id and cc.channel_id = pm.channel_id order by pm.prod_name , pm.channel_desc ;新语句的执行时间Elapsed: 00:00:00.14 （是原来的三分之一）新语句的执行计划SH@ prod> select * from table(dbms_xplan.display()) ;PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 134863587----------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 138 | 12696 | 84 (6)| 00:00:02 | | || 1 | SORT ORDER BY | | 138 | 12696 | 84 (6)| 00:00:02 | | ||* 2 | HASH JOIN | | 138 | 12696 | 83 (5)| 00:00:01 | | || 3 | VIEW | | 145 | 6670 | 38 (3)| 00:00:01 | | || 4 | HASH GROUP BY | | 145 | 2900 | 38 (3)| 00:00:01 | | || 5 | PARTITION RANGE ITERATOR | | 205 | 4100 | 33 (0)| 00:00:01 | KEY | KEY || 6 | TABLE ACCESS BY LOCAL INDEX ROWID | COSTS | 205 | 4100 | 33 (0)| 00:00:01 | KEY | KEY || 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | ||* 8 | BITMAP INDEX RANGE SCAN | COSTS_TIME_BIX | | | | | KEY | KEY || 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | || 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | || 11 | VIEW | | 205 | 9430 | 44 (5)| 00:00:01 | | || 12 | HASH UNIQUE | | 205 | 11890 | 44 (5)| 00:00:01 | | ||* 13 | HASH JOIN | | 205 | 11890 | 39 (3)| 00:00:01 | | || 14 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | || 15 | MERGE JOIN | | 205 | 5740 | 36 (3)| 00:00:01 | | || 16 | TABLE ACCESS BY INDEX ROWID | CHANNELS | 5 | 65 | 2 (0)| 00:00:01 | | || 17 | INDEX FULL SCAN | CHANNELS_PK | 5 | | 1 (0)| 00:00:01 | | ||* 18 | SORT JOIN | | 205 | 3075 | 34 (3)| 00:00:01 | | || 19 | PARTITION RANGE ITERATOR | | 205 | 3075 | 33 (0)| 00:00:01 | KEY | KEY || 20 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 205 | 3075 | 33 (0)| 00:00:01 | KEY | KEY || 21 | BITMAP CONVERSION TO ROWIDS | | | | | | | |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|* 22 | BITMAP INDEX RANGE SCAN | COSTS_TIME_BIX | | | | | KEY | KEY || 23 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | || 24 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |----------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("CC"."PROD_ID"="PM"."PROD_ID" AND "CC"."CHANNEL_ID"="PM"."CHANNEL_ID") 8 - access("C2"."TIME_ID">= (SELECT TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL") AND "C2"."TIME_ID"<= (SELECT TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL")) 13 - access("CO"."PROD_ID"="PR"."PROD_ID") 18 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID") filter("CH"."CHANNEL_ID"="CO"."CHANNEL_ID") 22 - access("CO"."TIME_ID">= (SELECT TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL") AND "CO"."TIME_ID"<= (SELECT TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') FROM "SYS"."DUAL" "DUAL"))45 rows selected.用复杂查询来代替PL/SQL程序
下面的PL/SQL块查询出来了3年以上的顾客的信息，并将其插入全局临时表中。
begin execute immediate 'truncate table cust3year' ; execute immediate 'truncate table sales3year' ; insert into cust3year select cust_id -- , count(cust_years) year_count from ( select distinct cust_id , trunc(time_id , 'year') cust_years from sh.sales ) group by cust_id having count(cust_years) >= 3 ; for crec in (select cust_id from cust3year) loop insert into sales3year select s.cust_id , p.prod_category , sum(co.unit_price*s.quantity_sold) from sh.sales s join sh.products p on p.prod_id = s.prod_id join sh.costs co on co.prod_id = s.prod_id and co.time_id = s.time_id join sh.customers cu on cu.cust_id = s.cust_id where s.cust_id = crec.cust_id group by s.cust_id , p.prod_category ; end loop ;end ;执行情况PL/SQL procedure successfully completed.Elapsed: 00:00:54.86查看结果SH@ prod> break on report SH@ prod> compute sum of total_sale on report SH@ prod> select c3.cust_id , c.cust_last_name , c.cust_first_name , s.prod_category , s.total_sale 2 from cust3year c3 3 join sales3year s on s.cust_id = c3.cust_id 4 join sh.customers c on c.cust_id = c3.cust_id 5 order by 1 , 4 ;用因子化的查询以及分析函数来完成上面这件事。
采用了因子间的嵌套，如果不使用因子很难完成。
with custyear as( select cust_id , extract(year from time_id) sales_year from sh.sales where extract(year from time_id ) between 1998 and 2002 group by cust_id , extract(year from time_id)) ,custselect as ( select distinct cust_id from ( select cust_id , count(*) over(partition by cust_id) year_count from custyear ) where year_count >= 3 )select cu.cust_id , cu.cust_last_name , cu.cust_first_name , p.prod_category , sum(co.unit_price * s.quantity_sold) total_sale from custselect csjoin sh.sales s on s.cust_id = cs.cust_idjoin sh.products p on p.prod_id = s.prod_idjoin sh.costs co on co.prod_id = s.prod_id and co.time_id = s.time_id join sh.customers cu on cu.cust_id = cs.cust_id group by cu.cust_id , cu.cust_last_name , cu.cust_first_name , p.prod_category order by cu.cust_id ;执行结果16018 rows selected.Elapsed: 00:00:07.66RSF递归子查询因子化（11.2中才出现）
对应ANSI中的recursive common table expression。
RSF与CONNECT BY
用CONNECT BY
HR@ prod> set linesize 180select lpad(' ' , level*2 - 1 , ' ' ) || emp.emp_last_name emp_last_name , emp.emp_first_name , emp.employee_id , emp.mgr_last_name , emp.mgr_first_name , emp.manager_id , department_name from ( select /*+ inline gather plan statistics */ e.last_name emp_last_name , e.first_name emp_first_name , e.employee_id , d.department_id , e.manager_id , d.department_name , es.last_name mgr_last_name , es.first_name mgr_first_name from hr.employees e left outer join hr.departments d on d.department_id = e.department_id left outer join hr.employees es on es.employee_id = e.manager_id ) empconnect by prior emp.employee_id = emp.manager_id start with emp.manager_id is nullorder siblings by emp.emp_last_name ;EMP_LAST_NAME EMP_FIRST_NAME EMPLOYEE_ID MGR_LAST_NAME MGR_FIRST_NAME MANAGER_ID DEPARTMENT_NAME------------------------------ -------------------- ----------- ------------------------- -------------------- ---------- ------------------------------ King Steven 100 Executive Cambrault Gerald 148 King Steven 100 Sales Bates Elizabeth 172 Cambrault Gerald 148 Sales Bloom Harrison 169 Cambrault Gerald 148 Sales Fox Tayler 170 Cambrault Gerald 148 Sales Kumar Sundita 173 Cambrault Gerald 148 Sales Ozer Lisa 168 Cambrault Gerald 148 Sales Smith William 171 Cambrault Gerald 148 Sales De Haan Lex 102 King Steven 100 Executive Hunold Alexander 103 De Haan Lex 102 IT Austin David 105 Hunold Alexander 103 IT Ernst Bruce 104 Hunold Alexander 103 IT Lorentz Diana 107 Hunold Alexander 103 IT Pataballa Valli 106 Hunold Alexander 103 IT Errazuriz Alberto 147 King Steven 100 Sales Ande Sundar 166 Errazuriz Alberto 147 Sales Banda Amit 167 Errazuriz Alberto 147 Sales Greene Danielle 163 Errazuriz Alberto 147 Sales Lee David 165 Errazuriz Alberto 147 Sales Marvins Mattea 164 Errazuriz Alberto 147 Sales Vishney Clara 162 Errazuriz Alberto 147 Sales Fripp Adam 121 King Steven 100 Shipping Atkinson Mozhe 130 Fripp Adam 121 Shipping Bissot Laura 129 Fripp Adam 121 Shipping Bull Alexis 185 Fripp Adam 121 Shipping Cabrio Anthony 187 Fripp Adam 121 Shipping Dellinger Julia 186 Fripp Adam 121 Shipping用RSF
with emp as ( select /*+ inline gather_plan_statistics */ e.last_name , e.first_name , e.employee_id , e.manager_id , d.department_name from hr.employees e left outer join hr.departments d on d.department_id = e.department_id ) ,emp_recurse(last_name , first_name , employee_id , manager_id , department_name , lv1) as( select e.last_name , e.first_name , e.employee_id , e.manager_id , e.department_name , 1 as lv1 from emp e where e.manager_id is null union all select emp.last_name , emp.first_name , emp.employee_id , emp.manager_id , emp.department_name , empr.lv1 + 1 as lv1 from emp join emp_recurse empr on empr.employee_id = emp.manager_id )search depth first by last_name set order1select lpad(' ' , lv1*2 - 1 , ' ' ) || er.last_name last_name , er.first_name , er.department_name from emp_recurse er ;LAST_NAME FIRST_NAME DEPARTMENT_NAME------------------------------ -------------------- ------------------------------ King Steven Executive Cambrault Gerald Sales Bates Elizabeth Sales Bloom Harrison Sales Fox Tayler Sales Kumar Sundita Sales Ozer Lisa Sales Smith William Sales De Haan Lex Executive Hunold Alexander IT Austin David IT Ernst Bruce IT Lorentz Diana IT Pataballa Valli IT Errazuriz Alberto Sales Ande Sundar Sales Banda Amit Sales Greene Danielle Sales Lee David Sales Marvins Mattea Sales Vishney Clara Sales Fripp Adam Shipping Atkinson Mozhe Shipping Bissot Laura Shipping Bull Alexis Shipping Cabrio Anthony Shipping Dellinger Julia Shipping阅读全文
0 0
- Oracle SQL高级编程——子查询因子化全解析
- Oracle SQL高级编程——Model子句全解析
- SQL高级编程-子查询
- oracle 子查询因子化 with as
- oracle递归子查询因子化
- T-SQL查询高级—SQL Server索引中的碎片和填充因子
- T-SQL查询高级—SQL Server索引中的碎片和填充因子
- T-SQL查询高级—SQL Server索引中的碎片和填充因子
- T-SQL查询高级—SQL Server索引中的碎片和填充因子
- T-SQL查询高级—SQL Server索引中的碎片和填充因子
- ORACLE SQL 高级编程--查询转换
- ORACLE 高级子查询
- Oracle-高级子查询
- oracle 高级子查询
- Oracle 高级子查询
- SQL子查询高级
- SQL编程之高级查询（子查询）以及注意事项
- oracle 子查询因子化 浅谈（with的使用）
- Ubuntu 14.04安装Docker
- Yarn核心——RPC(3) YarnRPC
- Spring Boot 2.0.0.M3使用案例，案例配置，常用命令，注解介绍，热部署
- hdu 5792 思维+树状数组
- bootstrapDualListbox 动态选中某一项
- Oracle SQL高级编程——子查询因子化全解析
- 反射能够干什么？使用反射编写通用jar包
- Hadoop学习2--搭建的教训总结
- bzoj 4996: [Usaco2017 Feb]Why Did the Cow Cross the Road II
- Atitit famous paint著名绘画 油画 作品数据库资料
- Spring-Boot根据配置文件生成Bean
- 偏最小二乘法回归
- 搭建ACE及TAO开发环境的快速方法(Linux+Docker篇)
- js设计模式之Facade
