PLSQL==>子查询因子化
来源：互联网 发布：js获取this对象 编辑：程序博客网 时间：2024/05/16 11:45
一.标准用法：当首次被引入的时候，with子句最有用的特性之一就是消除复杂的SQL查询，当一个查询中含大量的表和数据列时候，想要搞清楚查询中数据流向就变得很困难，通过使用子查询因子化，通过一个查询就可以将一些复杂的部分移到到主查询之外，从而使得查询更易于理解。
1.没有进行子查询因子化的交叉数据分析查询：
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(quantity_sold) quantity_sold
FROM sales
JOIN times
ON times.time_id = sales.time_id
JOIN customers
ON customers.cust_id = sales.cust_id
JOIN countries
ON countries.country_id = customers.country_id
JOIN 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;
Plan hash value: 1417402606
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 10165 (100)| | | |
| 1 | SORT GROUP BY PIVOT | | 1155 | 78540 | | 10165(1)| 00:00:08 | | |
| 2 | VIEW | | 46189 | 3067K| | 10165(1)| 00:00:08 | | |
| 3 | HASH GROUP BY | | 46189 | 4240K| 92M| 10165(1)| 00:00:08 | | |
|* 4 | HASH JOIN | | 918K| 82M| | 2545(1)| 00:00:02 | | |
| 5 | PART JOIN FILTER CREATE | :BF0000 | 1826 | 29216 | | 18(0)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | TIMES | 1826 | 29216 | | 18(0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 918K| 68M| | 2524(1)| 00:00:02 | | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | | 3(0)| 00:00:01 | | |
|* 9 | HASH JOIN | | 918K| 42M| | 2519(1)| 00:00:02 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 10 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | | 3(0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 918K| 28M| 1200K| 2513(1)| 00:00:02 | | |
| 12 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 405(1)| 00:00:01 | | |
| 13 | PARTITION RANGE JOIN-FILTER| | 918K| 20M| | 525(2)| 00:00:01 |:BF0000|:BF0000|
| 14 | TABLE ACCESS FULL | SALES | 918K| 20M| | 525(2)| 00:00:01 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TIMES"."TIME_ID"="SALES"."TIME_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 - access("PRODUCTS"."PROD_ID"="SALES"."PROD_ID")
9 - access("COUNTRIES"."COUNTRY_ID"="CUSTOMERS"."COUNTRY_ID")
11 - access("CUSTOMERS"."CUST_ID"="SALES"."CUST_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3151 consistent gets
3073 physical reads
0 redo size
110198 bytes sent via SQL*Net to client
5704 bytes received via SQL*Net from client
473 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
944 rows processed
进行子查询因子化的交叉表：
WITH sales_countries AS
(SELECT /*+ gather_plan_statistics */
cu.cust_id, co.country_name
FROM countries co, 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 sales s
JOIN times t
ON t.time_id = s.time_id
JOIN customers c
ON c.cust_id = s.cust_id
JOIN sales_countries sc
ON sc.cust_id = c.cust_id
JOIN 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;--增强SQL语句的可读性和可维护性
WITH和MATERIALIZE:
WITH cust AS
(SELECT /*+ materialize gather_plan_statistics */
b.cust_income_level, a.country_name
FROM customers b
JOIN sh.countries a
ON a.country_id = b.country_id)
SELECT country_name,
cust_income_level,
COUNT(country_name) country_cust_country
FROM cust c
HAVING COUNT (country_name) > (SELECT COUNT(*) * 0.1 FROM cust c2) OR COUNT (cust_income_level) >= (SELECT median(income_level_count)
FROM (SELECT cust_income_level,
COUNT(*) * 0.25 income_level_count
FROM cust
GROUP BY cust_income_level))
GROUP BY country_name, cust_income_level
ORDER BY 1, 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 266209621
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 620 | 495 (1)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6607_2150E6 | | | | | --临时表转换
|* 3 | HASH JOIN | | 55500 | 2222K| 408 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | SORT GROUP BY | | 20 | 620 | 87 (3)| 00:00:01 |
| 8 | VIEW | | 55500 | 1680K| 85 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | | | |
| 11 | VIEW | | 55500 | | 85 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
| 13 | SORT GROUP BY | | 1 | 13 | | |
| 14 | VIEW | | 12 | 156 | 87 (3)| 00:00:01 |
| 15 | SORT GROUP BY | | 12 | 252 | 87 (3)| 00:00:01 |
| 16 | VIEW | | 55500 | 1138K| 85 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6607_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*0.1 FROM(SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_2150E6"
"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(*)*0.25 "INCOME_LEVEL_COUNT" FROM (SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6607_2150E6"
"T1") "CUST" GROUP BY "CUST_INCOME_LEVEL") "from$_subquery$_006"))
Statistics
----------------------------------------------------------
4 recursive calls
314 db block gets
2375 consistent gets
1757 physical reads
576 redo size
2471 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed
使用WITH和INLINE提示：可以指导oracle不使用临时表转换就能满足这个查询的所有部分，
WITH cust AS
(SELECT /*+ inline gather_plan_statistics */
b.cust_income_level, a.country_name
FROM customers b
JOIN sh.countries a
ON a.country_id = b.country_id)
SELECT country_name,
cust_income_level,
COUNT(country_name) country_cust_country
FROM cust c
HAVING COUNT (country_name) > (SELECT COUNT(*) * 0.1 FROM cust c2) OR COUNT (cust_income_level) >= (SELECT median(income_level_count)
FROM (SELECT cust_income_level,
COUNT(*) * 0.25 income_level_count
FROM cust
GROUP BY cust_income_level))
GROUP BY country_name, cust_income_level
ORDER BY 1, 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2215075747
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 20 | 820 | 410 (1)| 00:00:01 |
|* 1 | FILTER || | | | |
| 2 | SORT GROUP BY || 20 | 820 | 410 (1)| 00:00:01 |
|* 3 | HASH JOIN || 55500 | 2222K| 408 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | COUNTRIES| 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS| 55500 | 1409K| 405 (1)| 00:00:01 |
| 6 | SORT AGGREGATE || 1 | 10 | ||
|* 7 | HASH JOIN || 55500 | 541K| 406 (1)| 00:00:01 |
| 8 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | CUSTOMERS| 55500 | 270K| 405 (1)| 00:00:01 |
| 10 | SORT GROUP BY || 1 | 13 | ||
| 11 | VIEW || 12 | 156 | 407 (1)| 00:00:01 |
| 12 | SORT GROUP BY || 12 | 528 | 407 (1)| 00:00:01 |
| 13 | NESTED LOOPS || 162 | 7128 | 407 (1)| 00:00:01 |
| 14 | VIEW | VW_GBF_8| 162 | 6318 | 407 (1)| 00:00:01 |
| 15 | SORT GROUP BY || 162 | 4212 | 407 (1)| 00:00:01 |
| 16 | TABLE ACCESS FULL| CUSTOMERS| 55500 | 1409K| 405 (1)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | 5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter(COUNT(*)> (SELECT COUNT(*)*0.1 FROM "SH"."COUNTRIES"
"A","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 "ITEM_3"
"CUST_INCOME_LEVEL",SUM("ITEM_2")*0.25 "INCOME_LEVEL_COUNT" FROM
"SH"."COUNTRIES" "A", (SELECT "B"."COUNTRY_ID" "ITEM_1",COUNT(*)
"ITEM_2","B"."CUST_INCOME_LEVEL" "ITEM_3" FROM "CUSTOMERS" "B" GROUP BY
"B"."CUST_INCOME_LEVEL","B"."COUNTRY_ID") "VW_GBF_8" WHERE
"A"."COUNTRY_ID"="ITEM_1" GROUP BY "ITEM_3") "from$_subquery$_006"))
3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
7 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
17 - access("A"."COUNTRY_ID"="ITEM_1")
Statistics
----------------------------------------------------------
172 recursive calls
0 db block gets
4694 consistent gets
4362 physical reads
0 redo size
2471 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
14 rows processed
WITH 和INLINE
WITH cust AS
(SELECT /*+ inline gathers_plan_statistics */
b.cust_income_level, a.country_name
FROM customers b
JOIN countries a
ON a.country_id = b.country_id),
median_income_set AS
(SELECT /*+ inlime */
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(*) * 0.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;
Elapsed: 00:00:05.82
Execution Plan
----------------------------------------------------------
Plan hash value: 1233954380
------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 20 | 820 | 410 (1)| 00:00:01 |
|* 1 | FILTER || | | | |
| 2 | HASH GROUP BY || 20 | 820 | 410 (1)| 00:00:01 |
|* 3 | HASH JOIN || 55500 | 2222K| 408 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | COUNTRIES| 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS| 55500 | 1409K| 405 (1)| 00:00:01 |
| 6 | SORT AGGREGATE || 1 | 10 | | |
|* 7 | HASH JOIN || 55500 | 541K| 406 (1)| 00:00:01 |
| 8 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | CUSTOMERS| 55500 | 270K| 405 (1)| 00:00:01 |
|* 10 | FILTER || | | | |
| 11 | HASH GROUP BY || 1 | 31 | 408 (1)| 00:00:01 |
|* 12 | HASH JOIN || 55500 | 1680K| 406 (1)| 00:00:01 |
| 13 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | CUSTOMERS| 55500 | 1409K| 405 (1)| 00:00:01 |
| 15 | SORT GROUP BY || 1 | 13 | | |
| 16 | VIEW || 12 | 156 | 407 (1)| 00:00:01 |
| 17 | SORT GROUP BY || 12 | 528 | 407 (1)| 00:00:01 |
| 18 | NESTED LOOPS || 162 | 7128 | 407 (1)| 00:00:01 |
| 19 | VIEW | VW_GBF_8| 162 | 6318 | 407 (1)| 00:00:01 |
| 20 | SORT GROUP BY | | 162 | 4212 | 407 (1)| 00:00:01 |
| 21 | TABLE ACCESS FULL| CUSTOMERS| 55500 | 1409K| 405 (1)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | COUNTRIES_PK | 1 | 5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)> (SELECT COUNT(*)*0.1 FROM "COUNTRIES" "A","CUSTOMERS" "B"
WHERE "A"."COUNTRY_ID"="B"."COUNTRY_ID") OR EXISTS (SELECT 0 FROM "COUNTRIES"
"A","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 "ITEM_3"
"CUST_INCOME_LEVEL",SUM("ITEM_2") "INCOME_LEVEL_COUNT" FROM "COUNTRIES" "A",
(SELECT "B"."COUNTRY_ID" "ITEM_1",COUNT(*) "ITEM_2","B"."CUST_INCOME_LEVEL"
"ITEM_3" FROM "CUSTOMERS" "B" GROUP BY "B"."CUST_INCOME_LEVEL","B"."COUNTRY_ID")
"VW_GBF_8" WHERE "A"."COUNTRY_ID"="ITEM_1" GROUP BY "ITEM_3")
"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 "ITEM_3" "CUST_INCOME_LEVEL",SUM("ITEM_2") "INCOME_LEVEL_COUNT"
FROM "COUNTRIES" "A", (SELECT "B"."COUNTRY_ID" "ITEM_1",COUNT(*)
"ITEM_2","B"."CUST_INCOME_LEVEL" "ITEM_3" FROM "CUSTOMERS" "B" GROUP BY
"B"."CUST_INCOME_LEVEL","B"."COUNTRY_ID") "VW_GBF_8" WHERE
"A"."COUNTRY_ID"="ITEM_1" GROUP BY "ITEM_3") "from$_subquery$_005"))
12 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
22 - access("A"."COUNTRY_ID"="ITEM_1")
Statistics
----------------------------------------------------------
172 recursive calls
0 db block gets
23635 consistent gets
23264 physical reads
0 redo size
15095 bytes sent via SQL*Net to client
1139 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
114 rows processed
WITH和MATERIALIZE:
WITH cust AS
(SELECT /*+ materialize gathers_plan_statistics */
b.cust_income_level, a.country_name
FROM customers b
JOIN countries a
ON a.country_id = b.country_id),
median_income_set AS
(SELECT /*+ inlime */
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(*) * 0.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;
Elapsed: 00:00:01.74
Execution Plan
----------------------------------------------------------
Plan hash value: 866637558
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 620 | 495 (1)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6609_2150E6 | | | | |
|* 3 | HASH JOIN | | 55500 | 2222K| 408 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1409K| 405 (1)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 20 | 620 | 87 (3)| 00:00:01 |
| 8 | VIEW | | 55500 | 1680K| 85 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | | | |
| 11 | VIEW | | 55500 | | 85 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
|* 13 | FILTER | | | | | |
| 14 | HASH GROUP BY | | 1 | 21 | 87 (3)| 00:00:01 |
| 15 | VIEW | | 55500 | 1138K| 85 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
| 17 | SORT GROUP BY | | 1 | 13 | | |
| 18 | VIEW | | 12 | 156 | 87 (3)| 00:00:01 |
| 19 | SORT GROUP BY | | 12 | 252 | 87 (3)| 00:00:01 |
| 20 | VIEW | | 55500 | 1138K| 85 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_2150E6 | 55500 | 1680K| 85(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."COUNTRY_ID"="B"."COUNTRY_ID")
6 - filter(COUNT("COUNTRY_NAME")> (SELECT COUNT(*)*0.1 FROM(SELECT /*+ CACHE_TEMP_TABLE
("T1") */ "C0" "CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6609_2150E6"
"T1") "C2") OR EXISTS (SELECT 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"CUST_INCOME_LEVEL","C1" "COUNTRY_NAME" FROM "SYS"."SYS_TEMP_0FD9D6609_2150E6" "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_0FD9D6609_2150E6" "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_0FD9D6609_2150E6" "T1") "CUST" GROUP BY "CUST_INCOME_LEVEL")
"from$_subquery$_005"))
Statistics
----------------------------------------------------------
44 recursive calls
314 db block gets
6357 consistent gets
1758 physical reads
576 redo size
15095 bytes sent via SQL*Net to client
1139 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
114 rows processed
计算成本的sql：
SELECT /*+ gather_plan_statistics */
substr(prod_name, 1, 30) prod_name,
channel_desc,
(SELECT AVG(c2.unit_cost)
FROM 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 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 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 channels ch, products pr, 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:03.32
Execution Plan
----------------------------------------------------------
Plan hash value: 1976423125
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20640 | 1310K| | 653 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 20 | | | | | |
| 2 | PARTITION RANGE ITERATOR | | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
| 5 | BITMAP AND | | | | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 |
| 7 | BITMAP MERGE | | | | | | | | |
|* 8 | BITMAP INDEX RANGE SCAN | COSTS_TIME_BIX | | | | | | 13 | 16 |
| 9 | SORT AGGREGATE | | 1 | 20 | | | | | |
| 10 | PARTITION RANGE ITERATOR | | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
| 12 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
| 13 | BITMAP AND | | | | | | | | |
|* 14 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 |
| 15 | BITMAP MERGE | | | | | | | | |
|* 16 | BITMAP INDEX RANGE SCAN | COSTS_TIME_BIX | | | | | | 13 | 16 |
| 17 | SORT AGGREGATE | | 1 | 20 | | | | | |
| 18 | PARTITION RANGE ITERATOR | | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| COSTS | 96 |1920 | | 26 (0)| 00:00:01 | 13 | 16 |
| 20 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
| 21 | BITMAP AND | | | | | | | | |
|* 22 | BITMAP INDEX SINGLE VALUE | COSTS_PROD_BIX | | | | | | 13 | 16 |
| 23 | BITMAP MERGE | | | | | | | | |
|* 24 | BITMAP INDEX RANGE SCAN | COSTS_TIME_BIX | | | | | | 13 | 16 |
| 25 | SORT ORDER BY | | 20640 | 1310K|1632K| 653 (1)| 00:00:01 | | |
| 26 | VIEW | | 20640 | 1310K| | 330 (1)| 00:00:01 | | |
| 27 | HASH UNIQUE | | 20640 | 1169K|1384K| 330 (1)| 00:00:01 | | |
|* 28 | HASH JOIN | | 20640 | 1169K| | 39 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS FULL | PRODUCTS | 72 |2160 | | 3 (0)| 00:00:01 | | |
|* 30 | HASH JOIN | | 20640 | 564K| | 36 (0)| 00:00:01 | | |
| 31 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 3 (0)| 00:00:01 | | |
| 32 | PARTITION RANGE ITERATOR | | 20640 | 302K| | 33 (0)| 00:00:01 | 13 | 16 |
|* 33 | TABLE ACCESS FULL | COSTS | 20640 | 302K| | 33 (0)| 00:00:01 | 13 | 16 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C2"."CHANNEL_ID"=:B1)
6 - access("C2"."PROD_ID"=:B1)
8 - access("C2"."TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C2"."TIME_ID"<=TO_DATE('
2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - filter("C2"."CHANNEL_ID"=:B1)
14 - access("C2"."PROD_ID"=:B1)
16 - access("C2"."TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C2"."TIME_ID"<=TO_DATE('
2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
19 - filter("C2"."CHANNEL_ID"=:B1)
22 - access("C2"."PROD_ID"=:B1)
24 - access("C2"."TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C2"."TIME_ID"<=TO_DATE('
2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
28 - access("CO"."PROD_ID"="PR"."PROD_ID")
30 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID")
33 - filter("CO"."TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
37281 consistent gets
0 physical reads
0 redo size
31537 bytes sent via SQL*Net to client
1700 bytes received via SQL*Net from client
109 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
216 rows processed
用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 channels ch, products pr, 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 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.53
Execution Plan
----------------------------------------------------------
Plan hash value: 4163084173
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14620 | 1313K| | 902 (1)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 14620 | 1313K| 1512K| 902 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 14620 | 1313K| | 589 (2)| 00:00:01 | | |
| 3 | VIEW | | 153 | 7038 | | 147 (3)| 00:00:01 | | |
| 4 | HASH GROUP BY | | 153 | 3060 | | 147 (3)| 00:00:01 | | |
| 5 | PARTITION RANGE ITERATOR | | 20640 | 403K| | 142 (2)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | COSTS | 20640 | 403K| | 142 (2)| 00:00:01 | KEY | KEY |
| 7 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 8 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 9 | VIEW | | 20640 | 927K| | 442 (1)| 00:00:01 | | |
| 10 | HASH UNIQUE | | 20640 | 1169K| 1384K| 442 (1)| 00:00:01 | | |
|* 11 | HASH JOIN | | 20640 | 1169K| | 148 (2)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | | 3 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 20640 | 564K| | 145 (2)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | CHANNELS |5 | 65 | |3 (0)| 00:00:01 | | |
| 15 | PARTITION RANGE ITERATOR| | 20640 | 302K| | 142 (2)| 00:00:01 | KEY | KEY |
|* 16 | TABLE ACCESS FULL | COSTS | 20640 | 302K| | 142 (2)| 00:00:01 | KEY | KEY |
| 17 | FAST DUAL | |1 | | | 2 (0)| 00:00:01 | | |
| 18 | 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")
6 - filter("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"))
11 - access("CO"."PROD_ID"="PR"."PROD_ID")
13 - access("CH"."CHANNEL_ID"="CO"."CHANNEL_ID")
16 - filter("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"))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
101 consistent gets
88 physical reads
0 redo size
31568 bytes sent via SQL*Net to client
1700 bytes received via SQL*Net from client
109 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
216 rows processed
将子查询因子化应用到PLSQL中：
用PLSQL来生成消费报告：
SQL> create global temporary table cust3year(cust_id number,cust_years number);
Table created.
Elapsed: 00:00:00.46
SQL> create global temporary table sales3year(cust_id number,prod_category varchar2(50),total_sale number);
Table created.
Elapsed: 00:00:00.07
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 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 sales s
JOIN products p
ON p.prod_id = s.prod_id
JOIN costs co
ON co.prod_id = s.prod_id
AND co.time_id = s.time_id
JOIN 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;
27 END;
28 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:59.63
SELECT c3.cust_id,
c.cust_last_name,
c.cust_first_name,
s.prod_category,
s.total_sale
FROM cust3year c3
JOIN sales3year s
ON s.cust_id = c3.cust_id
JOIN customers c
ON c.cust_id = c3.cust_id
ORDER BY 1, 4;
使用WITH生成消费报告：
WITH custyear AS
(SELECT cust_id, extract(YEAR FROM time_id) sales_year
FROM 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 -- 3 or more years as a customer during period
)
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 sales s
ON s.cust_id = cs.cust_id
JOIN products p
ON p.prod_id = s.prod_id
JOIN costs co
ON co.prod_id = s.prod_id
AND co.time_id = s.time_id
JOIN 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;
Elapsed: 00:00:20.40 --效率提高10倍
Execution Plan
----------------------------------------------------------
Plan hash value: 3974001180
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6941 | 372K| | 2844 (2)| 00:00:03 | | |
| 1 | SORT GROUP BY | | 6941 | 372K| 472K| 2844 (2)| 00:00:03 | | |
|* 2 | HASH JOIN | | 6941 | 372K| | 2747 (2)| 00:00:02 | | |
| 3 | VIEW | VW_GBC_13 | 6941 | 237K| | 2343 (2)| 00:00:02 | | |
| 4 | HASH GROUP BY | | 6941 | 427K| 13M| 2343 (2)| 00:00:02 | | |
|* 5 | HASH JOIN | | 199K| 11M| | 1209 (3)| 00:00:01 | | |
| 6 | VIEW | index$_join$_007 | 72 | 1512 | | 2(0)| 00:00:01 | | |
|* 7 | HASH JOIN | | | | | | | | |
| 8 | INDEX FAST FULL SCAN | PRODUCTS_PK | 72 | 1512 | | 1 (0)| 00:00:01 | | |
| 9 | INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX | 72 | 1512 | | 1(0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 199K| 8186K| | 1206 (3)| 00:00:01 | | |
| 11 | VIEW | | 1963 | 9815 | | 540 (5)| 00:00:01 | | |
| 12 | HASH UNIQUE | | 1963 | 29445 | | 540(5)| 00:00:01 | | |
|* 13 | VIEW | | 2297 | 34455 | | 539(4)| 00:00:01 | | |
| 14 | WINDOW BUFFER | | 2297 | 29861 | | 539 (4)| 00:00:01 | | |
| 15 | SORT GROUP BY | | 2297 | 29861 | | 539 (4)| 00:00:01 | | |
| 16 | PARTITION RANGE ALL| | 2297 | 29861 | | 537 (4)| 00:00:01 | 1 | 28 |
|* 17 | TABLE ACCESS FULL | SALES | 2297 | 29861 | | 537 (4)| 00:00:01 | 1 | 28 |
| 18 | PARTITION RANGE ALL | | 717K| 25M| | 665(2)| 00:00:01 | 1 | 28 |
|* 19 | HASH JOIN | | 717K| 25M| | 665(2)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | COSTS | 82112 | 1363K| | 137 (1)| 00:00:01 | 1 | 28 |
| 21 | TABLE ACCESS FULL | SALES | 918K| 17M| | 525 (2)| 00:00:01 | 1 | 28 |
| 22 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 405 (1)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CU"."CUST_ID"="ITEM_1")
5 - access("P"."PROD_ID"="S"."PROD_ID")
7 - access(ROWID=ROWID)
10 - access("S"."CUST_ID"="CS"."CUST_ID")
13 - filter("YEAR_COUNT">=3)
17 - filter(EXTRACT(YEAR FROM INTERNAL_FUNCTION("TIME_ID"))>=1998 AND EXTRACT(YEAR FROM
INTERNAL_FUNCTION("TIME_ID"))<=2002)
19 - access("CO"."TIME_ID"="S"."TIME_ID" AND "CO"."PROD_ID"="S"."PROD_ID")
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
4959 consistent gets
5793 physical reads
0 redo size
1978425 bytes sent via SQL*Net to client
88611 bytes received via SQL*Net from client
8010 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
16018 rows processed
- PLSQL==>子查询因子化
- 9.11 子查询因子化
- oracle 子查询因子化 with as
- connect by 递归子查询因子化
- 子查询因子化-WITH AS
- 递归子查询因子化-CONNECT BY
- oracle递归子查询因子化
- 使用子查询因子化(with..as)创建表
- oracle 子查询因子化 浅谈（with的使用）
- 递归公用表达式（子查询因子化）查询父代或子代（层次结构）
- Oracle SQL高级编程——子查询因子化全解析
- 影响因子查询网站
- 期刊影响因子查询
- plsql中的子类型
- PLSQL==>PACKAGE
- PLSQL==>>游标
- oracle怎么处理父子关系（递归查询因子化）
- PLSQL基础知识-合并查询
- JS从左边移动到右边
- define 用法总结
- 第一部分：Hibernate入门
- 测试一下图片
- iOS APNS远程推送(史上最全步骤)
- PLSQL==>子查询因子化
- AT 相关命令
- hdu5107 K-short Problem(线段树+离散化+思维)
- Gradle 教程说明 用户指南 第7章 构建Java工程----快速入门
- 使用第三方类库FMDB实现数据持久化
- 基于Spring + Spring MVC + Mybatis 高性能web构建
- Nginx进程分析（worker_process篇）
- C使用SQLite的例子
- 二叉链表的自我练习
