口试优化案例1-用分析函数优化优化执行计划中的FILTER以及标量子查询
面试优化案例1---用分析函数优化优化执行计划中的FILTER以及标量子查询
可以看到这个SQL 有三个问题
需求就是：访问 cy_product_sku 根据 product_code 分组,求出 每组 product_code 里面price 最小的 product_sku ,如果有两个不同的 product_sku 有相同的
最低price 那么就取 product_sku 最小的那个。取完以后 拿 各组的 product_code,product_sku 和外面的进行关联 其中 product_sku 和 cy_product_sku 表自关联
product_code 和cy_product_code 表关联。
至此，问题3被搞定了。
原SQL整体就被等价改为：
下面看一下新SQL的执行计划：
可以看到FILTER和标量子查询部分已经没有了，逻辑读从之前的75万减少到了14万。
最后的执行计划为：
今天在餐饮前端库抓出一条跑的很慢的分页语句，分页语句里没有对主表group by 这些禁忌，但是还跑6秒才跑完，逻辑读大概有 735634个buffers，立马给优化了一下。
SQL 语句如下：
SELECT *
FROM (SELECT (SELECT COUNT(1)
FROM cy_product_sku ps
WHERE ps.product_code = pc.product_code
AND ps.isdel = 0) AS skuNum,
pc.version,
pc.product_code,
pc.product_name,
ps.price,
pc.gross_weight,
pc.quantity,
pc.up_time,
pb.name AS ppName,
f_getcategory(pca.id) AS flName,
ps.product_num,
pt.name AS lxName,
pc.MAINIMGURL
FROM cy_product_code pc
LEFT JOIN cy_product_brand pb
ON pc.brand_id = pb.id
LEFT JOIN cy_product_category pca
ON pca.id = pc.category_id
LEFT JOIN cy_product_type pt
ON pc.type_id = pt.id
LEFT JOIN cy_product_sku ps
ON ps.product_code = pc.product_code
WHERE pc.isup = 1
AND pc.isdel = 0
AND ps.product_sku =
(SELECT MIN(product_sku)
FROM cy_product_sku
WHERE DEFAULT_SKU IN (2, 3)
AND isdel = 0
AND product_code = pc.product_code
AND price = (SELECT MIN(price)
FROM cy_product_sku
WHERE DEFAULT_SKU IN (2, 3)
AND isdel = 0
AND product_code = pc.product_code
GROUP BY PRODUCT_CODE)
GROUP BY product_code)
ORDER BY pc.up_time DESC)
WHERE rownum <= 10
执行计划为：
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2892 | 2663 (1)| 00:00:32 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL | CY_PRODUCT_SKU | 2 | 24 | 10 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 1 | 2892 | 2663 (1)| 00:00:32 |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 180 | 2663 (1)| 00:00:32 |
|* 6 | FILTER | | | | | |
|* 7 | HASH JOIN | | 1644 | 288K| 195 (0)| 00:00:03 |
| 8 | TABLE ACCESS FULL | CY_PRODUCT_SKU | 2027 | 77026 | 10 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 776 | 107K| 185 (0)| 00:00:03 |
| 10 | TABLE ACCESS FULL | CY_PRODUCT_CATEGORY | 532 | 2128 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 776 | 104K| 181 (0)| 00:00:03 |
| 12 | TABLE ACCESS FULL | CY_PRODUCT_BRAND | 35 | 455 | 4 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 776 | 97000 | 177 (0)| 00:00:03 |
| 14 | TABLE ACCESS FULL | CY_PRODUCT_TYPE | 31 | 496 | 4 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | CY_PRODUCT_CODE | 776 | 84584 | 173 (0)| 00:00:03 |
| 16 | SORT GROUP BY NOSORT | | 1 | 35 | 3 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| CY_PRODUCT_SKU | 1 | 35 | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_CY_PRO_SK_ISD_PRICE | 4 | | 1 (0)| 00:00:01 |
| 19 | SORT GROUP BY NOSORT | | 1 | 19 | 10 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | CY_PRODUCT_SKU | 1 | 19 | 10 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PS"."PRODUCT_CODE"=:B1 AND "PS"."ISDEL"=0)
3 - filter(ROWNUM<=10)
5 - filter(ROWNUM<=10)
6 - filter("PS"."PRODUCT_SKU"= (SELECT MIN("PRODUCT_SKU") FROM "CY_PRODUCT_SKU" "CY_PRODUCT_SKU"
WHERE "PRICE"= (SELECT MIN("PRICE") FROM "CY_PRODUCT_SKU" "CY_PRODUCT_SKU" WHERE "PRODUCT_CODE"=:B1
AND ("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) AND "ISDEL"=0 GROUP BY "PRODUCT_CODE") AND "ISDEL"=0 AND
"PRODUCT_CODE"=:B2 AND ("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) GROUP BY "PRODUCT_CODE"))
7 - access("PS"."PRODUCT_CODE"="PC"."PRODUCT_CODE")
9 - access("PCA"."ID"(+)=TO_NUMBER("PC"."CATEGORY_ID"))
11 - access("PB"."ID"(+)=TO_NUMBER("PC"."BRAND_ID"))
13 - access("PT"."ID"(+)=TO_NUMBER("PC"."TYPE_ID"))
15 - filter("PC"."ISUP"=1 AND "PC"."ISDEL"=0)
17 - filter("PRODUCT_CODE"=:B1 AND ("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3))
18 - access("ISDEL"=0 AND "PRICE"= (SELECT MIN("PRICE") FROM "CY_PRODUCT_SKU" "CY_PRODUCT_SKU"
WHERE "PRODUCT_CODE"=:B1 AND ("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) AND "ISDEL"=0 GROUP BY
"PRODUCT_CODE"))
20 - filter("PRODUCT_CODE"=:B1 AND ("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) AND "ISDEL"=0)
可以看到这个SQL 有三个问题
（1） select 里面有一个标量子查询 CY_PRODUCT_SKU 表将会扫描N次
（2） 这个分页框架是有问题的，这种分页框架，将会将所有的数据全部查询出来以后，然后order by 排序，最后一步再取前10行，也进而导致了 标量子查询里面的表被扫描N多次。
（3）执行计划里面有一个FILTER，而FILTER的第二个儿子，即ID=16往下的部分，将会被执行N多次，从SQL中看出，引起FILTER的原因就是这一段：
原SQL 里面的部分是：
SELECT MIN(product_sku)
FROM cy_product_sku ps1
WHERE ps1.DEFAULT_SKU IN (2, 3)
AND ps1.isdel = 0
AND product_code = pc.product_code -----和外面的pc 表根据product_code 关联 先去掉
AND ps1.price = (SELECT MIN(ps2.price)
FROM cy_product_sku ps2
WHERE ps2.DEFAULT_SKU IN (2, 3)
AND ps2.isdel = 0
AND ps2.product_code = ps1.product_code
GROUP BY ps2.PRODUCT_CODE)
GROUP BY ps1.product_code
需求就是：访问 cy_product_sku 根据 product_code 分组,求出 每组 product_code 里面price 最小的 product_sku ,如果有两个不同的 product_sku 有相同的
最低price 那么就取 product_sku 最小的那个。取完以后 拿 各组的 product_code,product_sku 和外面的进行关联 其中 product_sku 和 cy_product_sku 表自关联
product_code 和cy_product_code 表关联。
等价的意思就是：
SELECT MIN(product_sku), ps1.product_code
FROM cy_product_sku ps1
WHERE ps1.DEFAULT_SKU IN (2, 3)
AND ps1.isdel = 0
AND ps1.price = (SELECT MIN(ps2.price)
FROM cy_product_sku ps2
WHERE ps2.DEFAULT_SKU IN (2, 3)
AND ps2.isdel = 0
AND ps2.product_code = ps1.product_code
GROUP BY ps2.PRODUCT_CODE)
GROUP BY ps1.product_code
等价改写：
select product_sku, product_code
from (SELECT ROW_NUMBER() OVER(PARTITION BY product_code ORDER BY price, product_sku) ROW_NUM,
product_sku,
product_code
FROM cy_product_sku
WHERE DEFAULT_SKU IN (2, 3)
AND isdel = 0) RS
WHERE RS.ROW_NUM = 1;
至此，问题3被搞定了。
下面搞标量子查询：
由于select 里面还有个标量子查询，是对ps表的,而ps表本身就在left join里面因此 ps 表等价改成：
(select product_code,
product_sku,
price,
product_num,
COUNT(*) OVER(PARTITION BY product_code) cnt
from cy_product_sku
where isdel = 0) v_ps
原SQL整体就被等价改为：
SELECT *
FROM (SELECT v_ps.cnt AS skuNum,
pc.version,
pc.product_code,
pc.product_name,
v_ps.price,
pc.gross_weight,
pc.quantity,
pc.up_time,
pb.name AS ppName,
f_getcategory(pca.id) AS flName,
v_ps.product_num,
pt.name AS lxName,
pc.MAINIMGURL
FROM cy_product_code pc
LEFT JOIN cy_product_brand pb
ON pc.brand_id = pb.id
LEFT JOIN cy_product_category pca
ON pca.id = pc.category_id
LEFT JOIN cy_product_type pt
ON pc.type_id = pt.id
LEFT JOIN (select product_code,
product_sku,
price,
product_num,
COUNT(*) OVER(PARTITION BY product_code) cnt
from cy_product_sku
where isdel = 0) v_ps
ON v_ps.product_code = pc.product_code
inner join (select product_sku, product_code
from (SELECT ROW_NUMBER() OVER(PARTITION BY product_code ORDER BY price, product_sku) ROW_NUM,
product_sku,
product_code
FROM cy_product_sku
WHERE DEFAULT_SKU IN (2, 3)
AND isdel = 0) RS
WHERE RS.ROW_NUM = 1) v_tab
on pc.product_code = v_tab.product_code
and v_ps.product_sku = v_tab.product_sku
WHERE pc.isup = 1
AND pc.isdel = 0
ORDER BY pc.up_time DESC)
WHERE rownum <= 10
下面看一下新SQL的执行计划：
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2892 | 205 (2)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 2892 | 205 (2)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 483 | 205 (2)| 00:00:03 |
|* 4 | HASH JOIN OUTER | | 1 | 483 | 204 (1)| 00:00:03 |
| 5 | NESTED LOOPS OUTER | | 1 | 479 | 200 (1)| 00:00:03 |
|* 6 | HASH JOIN OUTER | | 1 | 466 | 199 (2)| 00:00:03 |
|* 7 | HASH JOIN | | 1 | 450 | 195 (2)| 00:00:03 |
|* 8 | HASH JOIN | | 807 | 115K| 184 (1)| 00:00:03 |
|* 9 | VIEW | | 995 | 37810 | 11 (10)| 00:00:01 |
|* 10 | WINDOW SORT PUSHED RANK| | 995 | 34825 | 11 (10)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | CY_PRODUCT_SKU | 995 | 34825 | 10 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | CY_PRODUCT_CODE | 776 | 84584 | 173 (0)| 00:00:03 |
| 13 | VIEW | | 1831 | 541K| 11 (10)| 00:00:01 |
| 14 | WINDOW SORT | | 1831 | 75071 | 11 (10)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | CY_PRODUCT_SKU | 1831 | 75071 | 10 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | CY_PRODUCT_TYPE | 31 | 496 | 4 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| CY_PRODUCT_BRAND | 1 | 13 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | CY_PRODUCT_BRAND_PK | 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | CY_PRODUCT_CATEGORY | 532 | 2128 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("PCA"."ID"(+)=TO_NUMBER("PC"."CATEGORY_ID"))
6 - access("PT"."ID"(+)=TO_NUMBER("PC"."TYPE_ID"))
7 - access("V_PS"."PRODUCT_SKU"="PRODUCT_SKU" AND "V_PS"."PRODUCT_CODE"="PC"."PRODUCT_CODE")
8 - access("PC"."PRODUCT_CODE"="PRODUCT_CODE")
9 - filter("RS"."ROW_NUM"=1)
10 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCT_CODE" ORDER BY
"PRICE","PRODUCT_SKU")<=1)
11 - filter(("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) AND "ISDEL"=0)
12 - filter("PC"."ISUP"=1 AND "PC"."ISDEL"=0)
15 - filter("ISDEL"=0)
18 - access("PB"."ID"(+)=TO_NUMBER("PC"."BRAND_ID"))
可以看到FILTER和标量子查询部分已经没有了，逻辑读从之前的75万减少到了14万。
下面再优化分页语句解决第二个问题：
首先创建相应的索引：
create index idx_yws on cy_product_code(up_time,isup,isdel); create index idx_yws2 on CY_PRODUCT_SKU(product_code); create index idx_yws3 on cy_product_type(id);
SQL 等价改写为：
select *
from (select *
from (select rownum rn, a.*
from (SELECT /*+ index_desc(pc idx_yws) use_nl(pc,v_ps) use_nl(pc,v_tab) use_nl(pc,pt) use_nl(pc,pca) leading(pc) */
v_ps.cnt AS skuNum,
pc.version,
pc.product_code,
pc.product_name,
v_ps.price,
pc.gross_weight,
pc.quantity,
pc.up_time,
pb.name AS ppName,
f_getcategory(pca.id) AS flName,
v_ps.product_num,
pt.name AS lxName,
pc.MAINIMGURL
FROM cy_product_code pc
LEFT JOIN cy_product_brand pb
ON pc.brand_id = pb.id
LEFT JOIN cy_product_category pca
ON pca.id = pc.category_id
LEFT JOIN cy_product_type pt
ON pc.type_id = pt.id
LEFT JOIN (select product_code,
product_sku,
price,
product_num,
COUNT(*) OVER(PARTITION BY product_code) cnt
from cy_product_sku
where isdel = 0) v_ps
ON v_ps.product_code = pc.product_code
inner join (select product_sku, product_code
from (SELECT ROW_NUMBER() OVER(PARTITION BY product_code ORDER BY price, product_sku) ROW_NUM,
product_sku,
product_code
FROM cy_product_sku
WHERE DEFAULT_SKU IN (2, 3)
AND isdel = 0) RS
WHERE RS.ROW_NUM = 1) v_tab
on pc.product_code = v_tab.product_code
and v_ps.product_sku = v_tab.product_sku
WHERE pc.isup = 1
AND pc.isdel = 0
ORDER BY pc.up_time DESC) a)
where rownum <= 10)
where rn >= 1
最后的执行计划为：
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2905 | 8041 (10)| 00:01:37 |
|* 1 | VIEW | | 1 | 2905 | 8041 (10)| 00:01:37 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1 | 2905 | 8041 (10)| 00:01:37 |
| 4 | COUNT | | | | | |
| 5 | VIEW | | 1 | 2892 | 8041 (10)| 00:01:37 |
| 6 | NESTED LOOPS OUTER | | 1 | 541 | 8041 (10)| 00:01:37 |
| 7 | NESTED LOOPS OUTER | | 1 | 537 | 8037 (10)| 00:01:37 |
| 8 | NESTED LOOPS OUTER | | 1 | 521 | 8036 (10)| 00:01:37 |
| 9 | NESTED LOOPS | | 1 | 508 | 8035 (10)| 00:01:37 |
| 10 | NESTED LOOPS | | 807 | 138K| 5613 (14)| 00:01:08 |
| 11 | TABLE ACCESS BY INDEX ROWID | CY_PRODUCT_CODE | 776 | 84584 | 2509 (1)| 00:00:31 |
|* 12 | INDEX FULL SCAN DESCENDING | IDX_YWS | 2709 | | 14 (0)| 00:00:01 |
|* 13 | VIEW PUSHED PREDICATE | | 1 | 67 | 4 (25)| 00:00:01 |
|* 14 | WINDOW SORT PUSHED RANK | | 1 | 35 | 4 (25)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| CY_PRODUCT_SKU | 1 | 35 | 3 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_YWS2 | 7 | | 1 (0)| 00:00:01 |
|* 17 | VIEW PUSHED PREDICATE | | 1 | 332 | 3 (0)| 00:00:01 |
| 18 | WINDOW BUFFER | | 2 | 82 | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | CY_PRODUCT_SKU | 2 | 82 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX_YWS2 | 7 | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | CY_PRODUCT_BRAND | 1 | 13 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | CY_PRODUCT_BRAND_PK | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | CY_PRODUCT_TYPE | 1 | 16 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | IDX_YWS3 | 1 | | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | CY_PRODUCT_CATEGORY | 1 | 4 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
12 - access("PC"."ISUP"=1 AND "PC"."ISDEL"=0)
filter("PC"."ISUP"=1 AND "PC"."ISDEL"=0)
13 - filter("RS"."ROW_NUM"=1)
14 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCT_CODE" ORDER BY "PRICE","PRODUCT_SKU")<=1)
15 - filter(("DEFAULT_SKU"=2 OR "DEFAULT_SKU"=3) AND "ISDEL"=0)
16 - access("PRODUCT_CODE"="PC"."PRODUCT_CODE")
17 - filter("V_PS"."PRODUCT_SKU"="PRODUCT_SKU")
19 - filter("ISDEL"=0)
20 - access("PRODUCT_CODE"="PC"."PRODUCT_CODE")
22 - access("PB"."ID"(+)=TO_NUMBER("PC"."BRAND_ID"))
24 - access("PT"."ID"(+)=TO_NUMBER("PC"."TYPE_ID"))
25 - filter("PCA"."ID"(+)=TO_NUMBER("PC"."CATEGORY_ID"))