Oracle SQL高级编程——子查询因子化全解析
程序员文章站
2022-04-20 22:02:36
概述
子查询因子化就是ansi中的公共表达式。
从11.2开始，子查询因子化开始支持递归。可以实现connect by的功能。
标准的子查询因子化的例子
这是一个非常复杂的查询，下面是不加因子化的版...
概述
子查询因子化就是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