update dt_allotapply_detaildb
set dt_allotapply_detaildb.userlock = '0000008050'
where (dt_allotapply_detaildb.userlock is null)
and exists(select 'A' from dt_allotapply_headerdb
where (dt_allotapply_detaildb.allotapplyno = dt_allotapply_headerdb.allotapplyno)
and (dt_allotapply_headerdb.billdateint >= 38836)
and (dt_allotapply_headerdb.billdateint <= 38854)
and (dt_allotapply_headerdb.companyid = '0000000013')
and (dt_allotapply_headerdb.deptid = '0000008689'))
and (dt_allotapply_detaildb.allotno is null or dt_allotapply_detaildb.allotno = '')
and exists(select 'A' from dt_product_cntldb a
where (dt_allotapply_detaildb.productid = a.productid)
and (a.companyid = '0000009121')
and (a.ctrlcompanyid = '0000000013'))其中dt_allotapply_detaildb是明细表,dt_allotapply_headerdb是主表,明细表越180万条记录,个人感觉是第一个exists语句造成的性能问题,不知各位高手有什么解决办法,很着急,谢谢!
set dt_allotapply_detaildb.userlock = '0000008050'
where (dt_allotapply_detaildb.userlock is null)
and exists(select 'A' from dt_allotapply_headerdb
where (dt_allotapply_detaildb.allotapplyno = dt_allotapply_headerdb.allotapplyno)
and (dt_allotapply_headerdb.billdateint >= 38836)
and (dt_allotapply_headerdb.billdateint <= 38854)
and (dt_allotapply_headerdb.companyid = '0000000013')
and (dt_allotapply_headerdb.deptid = '0000008689'))
and (dt_allotapply_detaildb.allotno is null or dt_allotapply_detaildb.allotno = '')
and exists(select 'A' from dt_product_cntldb a
where (dt_allotapply_detaildb.productid = a.productid)
and (a.companyid = '0000009121')
and (a.ctrlcompanyid = '0000000013'))其中dt_allotapply_detaildb是明细表,dt_allotapply_headerdb是主表,明细表越180万条记录,个人感觉是第一个exists语句造成的性能问题,不知各位高手有什么解决办法,很着急,谢谢!
2。字符串比较相对于数值比较慢,那几个带前置0的字段为何不to_number后再和数值比较呢?
然后关联临时表进行updateupdate dt_allotapply_detaildb a
set a.userlock = '0000008050'
where exists (select 1 from temp b where a.productid =b.productid )
这样服务器压力就小多了,也不需要运算那么多次,update本身就是很慢的操作再关联那么多条件当然很慢了。