下面是执行的SQL.用PLSQL分析的时候,COST 很底,但是CPU COST相当的高,一执行,就象死机一样,没有反映.不知道是什么原因.
由于这个语句是我们架构生成的,我没有办法修改FROM之前的任何信息,希望高手教我如何解决这个问题.
select count(1) as rowcount
FROM "AC20", "AB09", "AC01"
WHERE ab09.aab001 = ac20.aab001
and ab09.aae002 = ac20.aae002
and ac01.aac001 = ac20.aac001
and ab09.aae003 = ac20.aae003
and ab09.aae210 = ac20.aae210
and ab09.aae063 = ac20.aae063
and ab09.aae140 = ac20.aae140
and ab09.aae143 = ac20.aae143
and ab09.aac008 = ac20.aac008
and ab09.aae216 = ac20.aae216
and ab09.aae151 = '2'
AND ((ab09.aae003 >= '200710'))
AND ((ab09.aae003 <= '200710'))
AND ((ab09.aab200 = '20000178'))
ORDER BY ac20.aac001, ac20.aae140, ac20.aae003, ac20.aae143;
由于这个语句是我们架构生成的,我没有办法修改FROM之前的任何信息,希望高手教我如何解决这个问题.
select count(1) as rowcount
FROM "AC20", "AB09", "AC01"
WHERE ab09.aab001 = ac20.aab001
and ab09.aae002 = ac20.aae002
and ac01.aac001 = ac20.aac001
and ab09.aae003 = ac20.aae003
and ab09.aae210 = ac20.aae210
and ab09.aae063 = ac20.aae063
and ab09.aae140 = ac20.aae140
and ab09.aae143 = ac20.aae143
and ab09.aac008 = ac20.aac008
and ab09.aae216 = ac20.aae216
and ab09.aae151 = '2'
AND ((ab09.aae003 >= '200710'))
AND ((ab09.aae003 <= '200710'))
AND ((ab09.aab200 = '20000178'))
ORDER BY ac20.aac001, ac20.aae140, ac20.aae003, ac20.aae143;
好像那样就影响使用索引什么的。
我对此也是一知半解。
调整一下条件的顺序吧。
FROM "AC20", "AB09", "AC01"
WHERE rownum > 0
and ab09.aab001 = ac20.aab001
and ab09.aae002 = ac20.aae002
and ac01.aac001 = ac20.aac001
and ab09.aae003 = ac20.aae003
and ab09.aae210 = ac20.aae210
and ab09.aae063 = ac20.aae063
and ab09.aae140 = ac20.aae140
and ab09.aae143 = ac20.aae143
and ab09.aac008 = ac20.aac008
and ab09.aae216 = ac20.aae216
and ab09.aae151 = '2'
AND ((ab09.aae003 >= '200710'))
AND ((ab09.aae003 <= '200710'))
AND ((ab09.aab200 = '20000178'))
ORDER BY ac20.aac001, ac20.aae140, ac20.aae003, ac20.aae143;
把你目前的执行计划贴出来.
AND ((ab09.aae003 <= '200710'))
AND ((ab09.aab200 = '20000178'))
ORDER BY ac20.aac001, ac20.aae140, ac20.aae003, ac20.aae143;
==============
Order by 在这个SQL中没有任何用处,去掉
另外上面的and等价于,如果字段类型不匹配的话,考虑把隐式转换改为显式转换
AND ((ab09.aae003 = '200710'))
AND ((ab09.aab200 = '20000178'))