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