oracle where in优化,【求助】为什么在where过滤条件中使用in这样子查询后速度变的非常慢？！...

最新推荐文章于 2023-03-08 10:39:30 发布

xiaohu wang

最新推荐文章于 2023-03-08 10:39:30 发布

阅读量676

点赞数

文章标签： oracle where in优化

本帖最后由 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,