oracle用两个in查询特别慢,【求助】为什么在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

dd06a1abc3fb84da465ff4eff32374f6.gif

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

dd06a1abc3fb84da465ff4eff32374f6.gif

AL7.jpg (49.07 KB, 下载次数: 8)

2014-5-27 16:29 上传

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值