使用WITH AS 优化SQL

马上就要单身节了，正在想今年我去祸害谁家的姑娘，突然QQ好友发来信息，说能否帮忙优化一个SQL，SQL调优做得实在太多了，都已经麻木了，反正优化一个SQL也就几秒钟到几分钟的事情。

哥们说下面的SQL要跑5个多小时

SELECT B.AREA_ID, A.PARTY_ID, B.AREA_NAME, C.NAME CHANNEL_NAME, B.NAME PARTY_NAME, B.ACCESS_NUMBER, B.PROD_SPEC, B.START_DT, A.BO_ACTION_NAME, A.SO_STAFF_ID, A.ATOM_ACTION_ID, A.PROD_ID FROM DW_CHANNEL C, DW_CRM_DAY_USER B, DW_BO_ORDER A WHERE A.PROD_ID = B.PROD_ID AND A.CHANNEL_ID = C.CHANNEL_ID AND A.SO_STAFF_ID LIKE '36%' AND A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机', 'E8 - 2S','E6移动版', 'E9版1M(老版)', '普通E9','普通新版E8', '全省_紧密融合型E9套餐产品规格', '(新) 全省_紧密融合型E9套餐产品规格', '新春欢乐送之E8套餐', '新春欢乐送之E6套餐') AND NOT EXISTS (SELECT * FROM DW_BO_ORDER D WHERE D.STAFF_ID LIKE '36%' AND A.PARTY_ID = D.PARTY_ID AND A.BO_ID != D.BO_ID AND A.PROD_ID != D.PROD_ID AND A.BO_ACTION_NAME IN ('新装', '移机','资费变更') AND A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

下面是执行计划以及表信息

SQL> select count(*) from dw_bo_order; COUNT(*) ---------- 2282548 SQL> select count(*) from dw_crm_day_user; COUNT(*) ---------- 420918 SQL> select count(*) from dw_channel; COUNT(*) ---------- 48031 Plan hash value: 2142862569 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 905 | 121K| 4152K (2)| 13:50:32 | | | |* 1 | FILTER | | | | | | | | |* 2 | HASH JOIN | | 905 | 121K| 12616 (2)| 00:02:32 | | | |* 3 | HASH JOIN | | 905 | 99550 | 12448 (2)| 00:02:30 | | | | 4 | PARTITION RANGE ALL| | 1979 | 108K| 9168 (2)| 00:01:51 | 1 | 5 | |* 5 | TABLE ACCESS FULL | DW_BO_ORDER | 1979 | 108K| 9168 (2)| 00:01:51 | 1 | 5 | |* 6 | TABLE ACCESS FULL | DW_CRM_DAY_USER | 309K| 15M| 3277 (2)| 00:00:40 | | | | 7 | TABLE ACCESS FULL | DW_CHANNEL | 48425 | 1276K| 168 (1)| 00:00:03 | | | |* 8 | FILTER | | | | | | | | | 9 | PARTITION RANGE ALL | | 1 | 29 | 9147 (2)| 00:01:50 | 1 | 5 | |* 10 | TABLE ACCESS FULL | DW_BO_ORDER | 1 | 29 | 9147 (2)| 00:01:50 | 1 | 5 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DW_BO_ORDER" "D" WHERE (:B1='新装' OR :B2='移机' OR :B3='资费变更') AND "D"."PARTY_ID"=:B4 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."COMPLETE_DT">:B5-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B6 AND "D"."BO_ID"<>:B7)) 2 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID") 3 - access("A"."PROD_ID"="B"."PROD_ID") 5 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR "A"."BO_ACTION_NAME"='移机' OR "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE '36%') 6 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR "B"."PROD_SPEC"='E6移动版' OR "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR "B"."PROD_SPEC"='普通电话' OR "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机' OR "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐') 8 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更') 10 - filter("D"."PARTY_ID"=:B1 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."COMPLETE_DT">:B2-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B3 AND "D"."BO_ID"<>:B4)

有经验的人一看，一眼就知道这个SQL性能问题出在这里

NOT EXISTS (SELECT * FROM DW_BO_ORDER D WHERE D.STAFF_ID LIKE '36%' AND A.PARTY_ID = D.PARTY_ID AND A.BO_ID != D.BO_ID AND A.PROD_ID != D.PROD_ID AND A.BO_ACTION_NAME IN ('新装', '移机','资费变更') AND A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

你一定要注意看，前面的NOT EXISTS 里面套了 2个 != 尼玛，坑爹啊，神马业务逻辑啊，这个SQL太坑爹了，由于有!=的存在，CBO不能选择 HASH_AJ join的方式，只能走FILTER，哈哈，走FILTER绝对搞死人，不是吗？因为它要反复扫描 DW_BO_ORDER 非常多次，那么我建议那哥们把SQL改了，把里面的!=拆分，不过可惜的是，不管他怎么拆分，SQL业务逻辑总是不对，尼玛谁叫我们写SQL水平菜呢(自我批评一下)

于是建议他用下面的方法改写SQL

with D as (select /*+ materialize */ PARTY_ID,BO_ID,PROD_ID from DW_BO_ORDER where STAFF_ID LIKE '36%') SELECT B.AREA_ID, A.PARTY_ID, B.AREA_NAME, C.NAME CHANNEL_NAME, B.NAME PARTY_NAME, B.ACCESS_NUMBER, B.PROD_SPEC, B.START_DT, A.BO_ACTION_NAME, A.SO_STAFF_ID, A.ATOM_ACTION_ID, A.PROD_ID FROM DW_CHANNEL C, DW_CRM_DAY_USER B, DW_BO_ORDER A WHERE A.PROD_ID = B.PROD_ID AND A.CHANNEL_ID = C.CHANNEL_ID AND A.SO_STAFF_ID LIKE '36%' AND A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机', 'E8 - 2S','E6移动版', 'E9版1M(老版)', '普通E9','普通新版E8', '全省_紧密融合型E9套餐产品规格', '(新) 全省_紧密融合型E9套餐产品规格', '新春欢乐送之E8套餐', '新春欢乐送之E6套餐') AND NOT EXISTS (SELECT * FROM D WHERE D.STAFF_ID LIKE '36%' AND A.PARTY_ID = D.PARTY_ID AND A.BO_ID != D.BO_ID AND A.PROD_ID != D.PROD_ID AND A.BO_ACTION_NAME IN ('新装', '移机','资费变更') AND A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

执行计划和SQL执行时间如下：

SQL> set timi on SQL> WITH D AS 2 (SELECT /*+ materialize */ 3 PARTY_ID, 4 BO_ID, 5 PROD_ID, 6 COMPLETE_DT 7 FROM DW_BO_ORDER 8 WHERE STAFF_ID LIKE '36%' AND 9 BO_ACTION_NAME IN ('新装', 10 '移机', 11 '资费变更')) 12 SELECT 13 B.AREA_ID, 14 A.PARTY_ID, 15 B.AREA_NAME, 16 C.NAME CHANNEL_NAME, 17 B.NAME PARTY_NAME, 18 B.ACCESS_NUMBER, 19 B.PROD_SPEC, 20 B.START_DT, 21 A.BO_ACTION_NAME, 22 A.SO_STAFF_ID, 23 A.ATOM_ACTION_ID, 24 A.PROD_ID 25 FROM DW_CHANNEL C, 26 DW_CRM_DAY_USER B, 27 DW_BO_ORDER A 28 WHERE A.PROD_ID = B.PROD_ID AND 29 A.CHANNEL_ID = C.CHANNEL_ID AND 30 A.SO_STAFF_ID LIKE '36%' AND 31 A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND 32 B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机', 33 'E8 - 2S','E6移动版', 'E9版1M(老版)', 34 '普通E9','普通新版E8', 35 '全省_紧密融合型E9套餐产品规格', 36 '(新) 全省_紧密融合型E9套餐产品规格', 37 '新春欢乐送之E8套餐', 38 '新春欢乐送之E6套餐') AND 39 NOT EXISTS (SELECT * 40 FROM D 41 WHERE A.PARTY_ID = D.PARTY_ID AND 42 A.BO_ID != D.BO_ID AND 43 A.PROD_ID != D.PROD_ID AND 44 A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT); 已选择49245行。 已用时间: 00: 00: 12.37 执行计划 -------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2591883460 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 905 | 121K| 62428 (2)| 00:12:30 | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | 2 | LOAD AS SELECT | DW_BO_ORDER | | | | | | | | 3 | PARTITION RANGE ALL | | 114K| 3228K| 9127 (2)| 00:01:50 | 1 | 5 | |* 4 | TABLE ACCESS FULL | DW_BO_ORDER | 114K| 3228K| 9127 (2)| 00:01:50 | 1 | 5 | |* 5 | FILTER | | | | | | | | |* 6 | HASH JOIN | | 905 | 121K| 12616 (2)| 00:02:32 | | | |* 7 | HASH JOIN | | 905 | 99550 | 12448 (2)| 00:02:30 | | | | 8 | PARTITION RANGE ALL | | 1979 | 108K| 9168 (2)| 00:01:51 | 1 | 5 | |* 9 | TABLE ACCESS FULL | DW_BO_ORDER | 1979 | 108K| 9168 (2)| 00:01:51 | 1 | 5 | |* 10 | TABLE ACCESS FULL | DW_CRM_DAY_USER | 309K| 15M| 3277 (2)| 00:00:40 | | | | 11 | TABLE ACCESS FULL | DW_CHANNEL | 48425 | 1276K| 168 (1)| 00:00:03 | | | |* 12 | FILTER | | | | | | | | |* 13 | VIEW | | 114K| 6791K| 90 (3)| 00:00:02 | | | | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_D625B872 | 114K| 3228K| 90 (3)| 00:00:02 | | | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(TO_CHAR("STAFF_ID") LIKE '36%') 5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "STAFF_ID","C1" "PARTY_ID","C2" "BO_ID","C3" "PROD_ID","C4" "COMPLETE_DT" FROM "SYS"."SYS_TEMP_0FD9D662E_D625B872" "T1") "D" WHERE (:B1='新装' OR :B2='移机' OR :B3='资费变更') AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B4 AND "D"."BO_ID"<>:B5 AND "D"."PROD_ID"<>:B6 AND "D"."COMPLETE_DT">:B7-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0))) 6 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID") 7 - access("A"."PROD_ID"="B"."PROD_ID") 9 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR "A"."BO_ACTION_NAME"='移机' OR "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE '36%') 10 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR "B"."PROD_SPEC"='E6移动版' OR "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR "B"."PROD_SPEC"='普通电话' OR "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机' OR "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐') 12 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更') 13 - filter(TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B1 AND "D"."BO_ID"<>:B2 AND "D"."PROD_ID"<>:B3 AND "D"."COMPLETE_DT">:B4-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0)) 统计信息 ---------------------------------------------------------- 2 recursive calls 29 db block gets 110506 consistent gets 22 physical reads 656 redo size 2438096 bytes sent via SQL*Net to client 449 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 49245 rows processed

现在SQL 12秒可以跑完了，这个SQL优化到这里就行了，不能连接他的DB，妈的我业务逻辑也不清楚，奶奶的，神马时候帮别人优化一个SQL 一行一元。