使用WITH AS 优化SQL

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2011-11-28 09:32

字体：大中小 | 上一篇 | 下一篇 | 我要投稿

作者：robinson1988 来源：51Testing软件测试网采编

数据库

SQL

　　今天QQ好友突然发来信息，说能否帮忙优化一个SQL，SQL调优做得实在太多了，都已经麻木了，反正优化一个SQL也就几秒钟到几分钟的事情。

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

view plain copy to clipboard print ?

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);

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

view plain copy to clipboard print ?

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

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

| 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)

31/312 3 >

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选