oracle相同sql执行计划不同,相同的SQL 在正式和测试环境中执行计划的不同

相同的SQL 在两个环境里面执行计划不同 ，  由于 测试环境 数据行数比较小 ，执行很快，  而正式环境 数据量大 得执行6个多小时 ，大家帮忙看看差别在哪 ？ 多谢

语句大概是这样的 select   tab1.*    from  (a ,b) tab1 ,tab2 where tab1 tab2

select tab1.cardno cardno,tab1.totalamt totalamt,tab2.amt cardamt

from (

select /*count(*)*/  a.cardno cardno,a.curamt+nvl(b.tranamt,0) totalamt

from (

select /*count(*)*/ t1.cardno cardno,sum(nvl(t1.curamt,0)) curamt

from cardacct t1

where t1.cardstat = '00' and t1.openflag = '1'

/*and t1.carddate >= '20110101' and t1.carddate <= '20110110' */

/*and t1.cardno = '9000100100500697755'*/

group by t1.cardno

) a,

(

select tmp.cardno cardno,/*tmp.tranamt/100*/ sum(nvl(tmp.tranamt,0))/100 tranamt

from (

select t.cardno cardno,t.trantype trantype,(

case t.trantype

when 1 then (

case t.tranflag

when '2' then 0

else

t.curtranamt + t.histranamt

end

)

when 2 then (

case t.tranflag

when '2' then 0

else

0 - t.curtranamt - t.histranamt

end

)

when 3 then (

case t.tranflag

when '2' then 0

else

0 - t.curtranamt - t.histranamt

end

)

else 0

end

) as tranamt

from (

select t2.cardno cardno,t2.trantype trantype,t2.tranflag tranflag,t2.trandate,t2.trantime,

nvl(t2.tranamt,0) curtranamt,0 histranamt

from curtran t2

union all

select t3.cardno cardno,t3.trantype trantype,t3.tranflag tranflag,t3.trandate,t3.trantime,

0 curtranamt,nvl(t3.tranamt,0) histranamt

from histran t3

) t

) tmp

/*where tmp.cardno = '9000100188803462573'*/

group by tmp.cardno

) b

where a.cardno = b.cardno(+)

) tab1,(

select /*count(*)*/ b.startno,b.endno,b.amt

from buycardbill a,sellcardlist b

where a.id = b.buycardbillid and a.flag not in ('0','2','5','6')

/*and to_char(a.sellcarddate,'yyyymmdd') >= '20110101' and to_char(a.sellcarddate,'yyyymmdd') <= '20110110'*/

/*and  b.startno <= '9000100188803462573' and b.endno >= '9000100188803462573'*/

) tab2

where tab1.cardno >= tab2.startno and tab1.cardno <= tab2.endno

and (tab1.totalamt > tab2.amt or tab1.totalamt <> tab2.amt);

[本帖最后由 lgyy 于 2011-9-9 16:04 编辑]

1.jpg

(117.53 KB, 下载次数: 2)

2011-9-9 16:00 上传

正式

2.jpg

(94.43 KB, 下载次数: 2)

2011-9-9 16:00 上传

测试