有经验的人一看，一眼就知道这个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); |
