本帖最后由 yujian0902 于 2014-5-27 16:33 编辑
有次帮同事优化SQL,发现写的SQL使用in这种过滤方式,查询出来花了将近6分钟;而后我改成连接方式(join),不到一秒钟。为什么反差如此之大?!
原SQL:
SELECT *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT LV2.DSTC_ID AreaId,
(select b.name from area_info_center@srcdb b where b.regoinid = lv2.dstc_id and rownum = 1) AreaName,
LV2.TIME_ID Time,
LV2.SAMPLE_ALL,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.RSCP_ALL / LV2.SAMPLE_ALL),2) RSCP_AVG,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.GOODCOVER1 / LV2.SAMPLE_ALL),4) KEY1,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.GOODCOVER2 / LV2.SAMPLE_ALL),4) KEY2,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.GOODCOVER3 / LV2.SAMPLE_ALL),4) KEY3,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.GOODCOVER4 / LV2.SAMPLE_ALL),4) KEY4,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.GOODCOVER5 / LV2.SAMPLE_ALL),4) KEY5,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY1 / LV2.SAMPLE_ALL),4) KEY6,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY2 / LV2.SAMPLE_ALL),4) KEY7,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY3 / LV2.SAMPLE_ALL),4) KEY8,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY4 / LV2.SAMPLE_ALL),4) KEY9,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY5 / LV2.SAMPLE_ALL),4) KEY10,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY6 / LV2.SAMPLE_ALL),4) KEY11,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY7 / LV2.SAMPLE_ALL),4) KEY12,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY8 / LV2.SAMPLE_ALL),4) KEY13,
ROUND(DECODE(LV2.SAMPLE_ALL,0,0,LV2.LV1KEY9 / LV2.SAMPLE_ALL),4) KEY14
FROM (SELECT T.DSTC_ID,
T.TIME_ID,
SUM(CASE WHEN (T.ZONE_ID = 0 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) SAMPLE_ALL,
SUM(CASE WHEN (T.ZONE_ID = 0 AND T.IDT_ID = 10000039) THEN T.IDT_VAL ELSE 0 END) RSCP_ALL,
SUM(CASE WHEN (T.ZONE_ID = 1 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER1,
SUM(CASE WHEN (T.ZONE_ID = 2 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER2,
SUM(CASE WHEN (T.ZONE_ID = 3 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER3,
SUM(CASE WHEN (T.ZONE_ID = 4 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER4,
SUM(CASE WHEN (T.ZONE_ID = 5 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER5,
SUM(CASE WHEN (T.ZONE_ID = 1 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY1,
SUM(CASE WHEN (T.ZONE_ID = 2 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY2,
SUM(CASE WHEN (T.ZONE_ID = 3 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY3,
SUM(CASE WHEN (T.ZONE_ID = 4 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY4,
SUM(CASE WHEN (T.ZONE_ID = 5 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY5,
SUM(CASE WHEN (T.ZONE_ID = 6 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY6,
SUM(CASE WHEN (T.ZONE_ID = 7 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY7,
SUM(CASE WHEN (T.ZONE_ID = 8 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY8,
SUM(CASE WHEN (T.ZONE_ID = 9 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY9
FROM CTUNI_SYS.G_HOUR_DSTC_NET_VSN_ZONE T
WHERE 1 = 1
and t.time_type = 3
and t.time_id >= '2014-04'
and t.time_id <= '2014-05'
and t.hour_id = '0'
and (t.DSTC_ID = 1919 or
t.DSTC_ID in
(select b.regoinid
from area_info_center@srcdb b
where b.parentregoinid = 1919))
and t.vsn = '0'
and t.net_type = 5
GROUP BY T.DSTC_ID, T.TIME_ID) LV2
order by lv2.dstc_id asc, lv2.time_id desc) A
WHERE ROWNUM <= 10)
WHERE RN >= 1
WO8.jpg (39.88 KB, 下载次数: 11)
原SQL执行计划
2014-5-27 16:24 上传
优化SQL:
SELECT *
FROM (SELECT ROWNUM RN,A.*
FROM (SELECT LV2.DSTC_ID AreaId,
(select b.name from area_info_center@srcdb b where b.regoinid=lv2.dstc_id and rownum=1) AreaName,LV2.TIME_ID Time,
LV2.SAMPLE_ALL,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.RSCP_ALL / LV2.SAMPLE_ALL), 2) RSCP_AVG,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.GOODCOVER1 / LV2.SAMPLE_ALL), 4) KEY1,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.GOODCOVER2 / LV2.SAMPLE_ALL), 4) KEY2,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.GOODCOVER3 / LV2.SAMPLE_ALL), 4) KEY3,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.GOODCOVER4 / LV2.SAMPLE_ALL), 4) KEY4,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.GOODCOVER5 / LV2.SAMPLE_ALL), 4) KEY5,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY1 / LV2.SAMPLE_ALL), 4) KEY6,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY2 / LV2.SAMPLE_ALL), 4) KEY7,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY3 / LV2.SAMPLE_ALL), 4) KEY8,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY4 / LV2.SAMPLE_ALL), 4) KEY9,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY5 / LV2.SAMPLE_ALL), 4) KEY10,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY6 / LV2.SAMPLE_ALL), 4) KEY11,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY7 / LV2.SAMPLE_ALL), 4) KEY12,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY8 / LV2.SAMPLE_ALL), 4) KEY13,
ROUND(DECODE(LV2.SAMPLE_ALL, 0, 0, LV2.LV1KEY9 / LV2.SAMPLE_ALL), 4) KEY14
FROM (SELECT T.DSTC_ID,T.TIME_ID,
SUM(CASE WHEN (T.ZONE_ID = 0 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) SAMPLE_ALL,
SUM(CASE WHEN (T.ZONE_ID = 0 AND T.IDT_ID = 10000039) THEN T.IDT_VAL ELSE 0 END) RSCP_ALL,
SUM(CASE WHEN (T.ZONE_ID = 1 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER1,
SUM(CASE WHEN (T.ZONE_ID = 2 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER2,
SUM(CASE WHEN (T.ZONE_ID = 3 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER3,
SUM(CASE WHEN (T.ZONE_ID = 4 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER4,
SUM(CASE WHEN (T.ZONE_ID = 5 AND T.IDT_ID = 10000040) THEN T.IDT_VAL ELSE 0 END) GOODCOVER5,
SUM(CASE WHEN (T.ZONE_ID = 1 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY1,
SUM(CASE WHEN (T.ZONE_ID = 2 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY2,
SUM(CASE WHEN (T.ZONE_ID = 3 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY3,
SUM(CASE WHEN (T.ZONE_ID = 4 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY4,
SUM(CASE WHEN (T.ZONE_ID = 5 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY5,
SUM(CASE WHEN (T.ZONE_ID = 6 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY6,
SUM(CASE WHEN (T.ZONE_ID = 7 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY7,
SUM(CASE WHEN (T.ZONE_ID = 8 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY8,
SUM(CASE WHEN (T.ZONE_ID = 9 AND T.IDT_ID = 10000047) THEN T.IDT_VAL ELSE 0 END) LV1KEY9
FROM CTUNI_SYS.G_HOUR_DSTC_NET_VSN_ZONE T,
(select 1918 regoinid from dual
union
select b.regoinid from area_info_center@srcdb b where b.parentregoinid=1918) S
WHERE 1 = 1
and t.time_type=3 and t.time_id>='2014-04' and t.time_id<='2014-05' and t.hour_id = '0'
and t.DSTC_ID = s.regoinid
and t.vsn='0' and t.net_type=5
GROUP BY T.DSTC_ID,T.TIME_ID) LV2
order by lv2.dstc_id asc,lv2.time_id desc) A
WHERE ROWNUM<=10) WHERE RN>=1
AL7.jpg (49.07 KB, 下载次数: 8)
2014-5-27 16:29 上传