Oracle OR 和 UNION ALL效率问题

[java豆]

各位好，我最近遇到这样一个问题。请大家帮我分析下原因。谢谢。
前提是客户数据量很大。
客户执行我写的一段SQL，结果执行了5分钟才执行完。
SELECT TABLE1.STRK,
TABLE1.ITM,
TABLE1.LT,
TABLE1.LC,
TABLE1.ID,
TABLE2.LOGICALLO,
TABLE1.QTY,
TABLE3.CCI,
'NO' UPTCC,
TABLE4.RTKEY
FROM TABLE4, TABLE1, TABLE5, TABLE2, TABLE3
WHERE TABLE4.PROCESSFLAG = '0'
AND TABLE1.STRK >= TABLE4.STRKSTART
AND TABLE1.STRK <= TABLE4.STRKEND
AND TABLE1.ITM >= TABLE4.ITMSTART
AND TABLE1.ITM <= TABLE4.ITMEND
AND TABLE1.LC >= TABLE4.LCSTART
AND TABLE1.LC <= TABLE4.LCEND
AND TABLE1.STRK = TABLE3.STRK
AND TABLE1.STRK = ITMXTABLE2.STRK
AND TABLE1.ITM = ITMXTABLE2.ITM
AND TABLE1.LC = ITMXTABLE2.LC
AND TABLE1.LC = TABLE2.LC
AND TABLE1.QTY > 0
AND TABLE2.LCLEVEL >= TABLE4.LCLEVELSTART
AND TABLE2.LCLEVEL <= TABLE4.LCLEVELEND
AND TABLE2.LCT NOT IN
('f', 'I', 'O', 'P', 'ND', 'RT', 'AGED')
AND TABLE2.PTZ >= TABLE4.ZONESTART
AND TABLE2.PTZ <= TABLE4.ZONEEND
AND TABLE2.PTZ IN
(SELECT PTZ
FROM ARADT
WHERE ARKEY >= TABLE4.ARSTART
AND ARKEY <= TABLE4.AREND)
AND TABLE3.CCTABLE5 <> '1'
AND TABLE3.TYPE = '1'
AND TABLE1.QTY > 0
AND TABLE1.QTY <= 2.147483647E9
AND TABLE4.RTKEY = '0000000031'
AND ((TABLE4.omv = '0') OR
(TABLE4.omv = '1' AND
TABLE1.ID IN
(SELECT ID
FROM V_CCID
WHERE V_CCID.ADDDATE >= TABLE4.STARTDATE
AND V_CCID.ADDDATE <= TABLE4.ENDDATE)))
ORDER BY TABLE4.RTKEY,
TABLE1.STRK,
TABLE1.ITM,
TABLE1.LC


其中红色部分是执行慢的原因，后来我改成UNION ALL ，只用了几秒钟。


SELECT STRK,
ITM,
LT,
LC,
ID,
LOGICALLO,
QTY,
CCI,
'UPTCC,
RTKEY
from ( SELECT
TABLE1.STRK,
TABLE1.ITM,
TABLE1.LT,
TABLE1.LC,
TABLE1.ID,
TABLE2.LOGICALLO,
TABLE1.QTY,
TABLE3.CCI,
'NO' UPTCC,
TABLE4.RTKEY
FROM TABLE4, TABLE1, TABLE5, TABLE2, TABLE3
WHERE TABLE4.PROCESSFLAG = '0'
AND TABLE1.STRK >= TABLE4.STRKSTART
AND TABLE1.STRK <= TABLE4.STRKEND
AND TABLE1.ITM >= TABLE4.ITMSTART
AND TABLE1.ITM <= TABLE4.ITMEND
AND TABLE1.LC >= TABLE4.LCSTART
AND TABLE1.LC <= TABLE4.LCEND
AND TABLE1.STRK = TABLE3.STRK
AND TABLE1.STRK = ITMXTABLE2.STRK
AND TABLE1.ITM = ITMXTABLE2.ITM
AND TABLE1.LC = ITMXTABLE2.LC
AND TABLE1.LC = TABLE2.LC
AND TABLE1.QTY > 0
AND TABLE2.LCLEVEL >= TABLE4.LCLEVELSTART
AND TABLE2.LCLEVEL <= TABLE4.LCLEVELEND
AND TABLE2.LCT NOT IN
('f', 'I', 'O', 'P', 'ND', 'RT', 'AGED')
AND TABLE2.PTZ >= TABLE4.ZONESTART
AND TABLE2.PTZ <= TABLE4.ZONEEND
AND TABLE2.PTZ IN
(SELECT PTZ
FROM ARADT
WHERE ARKEY >= TABLE4.ARSTART
AND ARKEY <= TABLE4.AREND)
AND TABLE3.CCTABLE5 <> '1'
AND TABLE3.TYPE = '1'
AND TABLE1.QTY > 0
AND TABLE1.QTY <= 2.147483647E9
AND TABLE4.RTKEY = '0000000031'
AND TABLE4.omv = '0'

UNION ALL

SELECT TABLE1.STRK,
TABLE1.ITM,
TABLE1.LT,
TABLE1.LC,
TABLE1.ID,
TABLE2.LOGICALLO,
TABLE1.QTY,
TABLE3.CCI,
'NO' UPTCC,
TABLE4.RTKEY
FROM TABLE4, TABLE1, TABLE5, TABLE2, TABLE3
WHERE TABLE4.PROCESSFLAG = '0'
AND TABLE1.STRK >= TABLE4.STRKSTART
AND TABLE1.STRK <= TABLE4.STRKEND
AND TABLE1.ITM >= TABLE4.ITMSTART
AND TABLE1.ITM <= TABLE4.ITMEND
AND TABLE1.LC >= TABLE4.LCSTART
AND TABLE1.LC <= TABLE4.LCEND
AND TABLE1.STRK = TABLE3.STRK
AND TABLE1.STRK = ITMXTABLE2.STRK
AND TABLE1.ITM = ITMXTABLE2.ITM
AND TABLE1.LC = ITMXTABLE2.LC
AND TABLE1.LC = TABLE2.LC
AND TABLE1.QTY > 0
AND TABLE2.LCLEVEL >= TABLE4.LCLEVELSTART
AND TABLE2.LCLEVEL <= TABLE4.LCLEVELEND
AND TABLE2.LCT NOT IN
('f', 'I', 'O', 'P', 'ND', 'RT', 'AGED')
AND TABLE2.PTZ >= TABLE4.ZONESTART
AND TABLE2.PTZ <= TABLE4.ZONEEND
AND TABLE2.PTZ IN
(SELECT PTZ
FROM ARADT
WHERE ARKEY >= TABLE4.ARSTART
AND ARKEY <= TABLE4.AREND)
AND TABLE3.CCTABLE5 <> '1'
AND TABLE3.TYPE = '1'
AND TABLE1.QTY > 0
AND TABLE1.QTY <= 2.147483647E9
AND TABLE4.RTKEY = '0000000031'
AND TABLE4.omv = '1' AND
TABLE1.ID IN
(SELECT ID
FROM V_CCID
WHERE V_CCID.ADDDATE >= TABLE4.STARTDATE
AND V_CCID.ADDDATE <= TABLE4.ENDDATE)
)

ORDER BY RTKEY,
STRK,
ITM,
LC

[流浪川]

这个从效率上来说，因素很多，看具体执行计划，是否走索引。 正常习惯我是喜欢将多个or改写成union来使用。但是在走索引的时候，其实还是or的效率高一些。 所以个人认为不固定，看具体环境下那个快用哪个呗。。

[流浪川]

归根结底，我的判断依据就是是否走索引，是否跑的快。。别的我还真说不出道理来。。

[sych888]

看看执行计划差异在什么地方

[bw555]

楼上正解，从执行计划中查找时间消耗的差异是最快的 测试的时候可以先将其他一些无关的表和条件去掉，否则执行计划看起来就太乱了

[小灰狼W]

就是由于cost估算的不够准确，以及统计信息的不完整 例如当你用where a=1 or a=2时，cbo判断走a的索引并不合适，走全表扫描更好 当改写成 select ... from ... where a=1 union all select ... from .. where a=2 这是两个语句的拼接，各自有自己的执行计划，走a字段的索引就在情理之中 当条件更复杂的时候，cbo的误判就可能更加严重

[现在]

good good study,day day up.