概述
子查询因子化就是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 25 model c9827b cordless phone battery turkey model c9827b cordless phone battery united kingdom 17 23 45 model c9827b cordless phone battery united states of america 151 310 522 model cd13272 tricolor ink cartridge argentina model cd13272 tricolor ink cartridge australia 16 17 39 model cd13272 tricolor ink cartridge brazil model cd13272 tricolor ink cartridge canada 12 20 26 model cd13272 tricolor ink cartridge denmark 10 15 19 model cd13272 tricolor ink cartridge france 15 14 27 model cd13272 tricolor ink cartridge germany 28 35 64 model cd13272 tricolor ink cartridge italy 27 23 45 model cd13272 tricolor ink cartridge japan 24 31 73 model cd13272 tricolor ink cartridge singapore 13 20 33 model cd13272 tricolor ink cartridge spain 11 8 17 model cd13272 tricolor ink cartridge turkey model cd13272 tricolor ink cartridge united kingdom 16 30 53 model cd13272 tricolor ink cartridge united states of america 244 314 629 model k3822l cordless phone battery argentina model 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 c having 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 c having 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.22 sys@ 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 c having 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 181 poland h: 150,000 - 169,999 61 singapore h: 150,000 - 169,999 50 new zealand h: 150,000 - 169,999 21 brazil e: 90,000 - 109,999 105 denmark e: 90,000 - 109,999 61 114 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 c having 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 181 poland h: 150,000 - 169,999 61 singapore h: 150,000 - 169,999 50 new zealand h: 150,000 - 169,999 21 brazil e: 90,000 - 109,999 105 denmark e: 90,000 - 109,999 61 114 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') ) c order 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 pm join 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 cs join sh.sales s on s.cust_id = cs.cust_id 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 = 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.66
rsf递归子查询因子化(11.2中才出现)
对应ansi中的recursive common table expression。
rsf与connect by
用connect by
hr@ prod> set linesize 180 select 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 ) emp connect by prior emp.employee_id = emp.manager_id start with emp.manager_id is null order 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 order1 select 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