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          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
(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐