说明:test_target表中的数据是3000多条,test_target表中的是2500万条.怎么优化下面语句,让执行效率变的更高.update test_target t1
set (t1.togetvalues,t1.algetvalues,t1.nogetvalue) =
(select t2.togetvalues,t2.algetvalues,t2.nogetvalue
from test_source t2,test_target t3
where t2.company_id = 6070000
and t2.company_id = t3.company_id
and t2.id = t3.id
and t2.check_date_id = t3.check_date_id
and t2.relationid = t3.relationid
and t2.dept_id = t3.dept_id
and t2.sub_id = t3.sub_id
and t3.pay_detail_id = t1.pay_detail_id)
where t1.pay_detail_id =
(select t5.pay_detail_id
from test_source t4,test_target t5
where t4.company_id = 6070000
and t4.company_id = t5.company_id
and t4.id = t5.id
and t4.check_date_id = t5.check_date_id
and t4.relationid = t5.relationid
and t4.dept_id = t5.dept_id
and t4.sub_id = t5.sub_id
and t5.pay_detail_id = t1.pay_detail_id)
set (t1.togetvalues,t1.algetvalues,t1.nogetvalue) =
(select t2.togetvalues,t2.algetvalues,t2.nogetvalue
from test_source t2,test_target t3
where t2.company_id = 6070000
and t2.company_id = t3.company_id
and t2.id = t3.id
and t2.check_date_id = t3.check_date_id
and t2.relationid = t3.relationid
and t2.dept_id = t3.dept_id
and t2.sub_id = t3.sub_id
and t3.pay_detail_id = t1.pay_detail_id)
where t1.pay_detail_id =
(select t5.pay_detail_id
from test_source t4,test_target t5
where t4.company_id = 6070000
and t4.company_id = t5.company_id
and t4.id = t5.id
and t4.check_date_id = t5.check_date_id
and t4.relationid = t5.relationid
and t4.dept_id = t5.dept_id
and t4.sub_id = t5.sub_id
and t5.pay_detail_id = t1.pay_detail_id)
解决方案 »
- oracle 存储过程 集合 和循环遍历的问题
- ORACLE中distinct函数后去指定的函数(急)
- 我想知道Oracle的一此包的說明與使用使用方法,如何查找,哪里有這種幫助!!!,如我想查找包dbms_Obfuscation_toolkit 的一此用法,怎么查找,
- 求toad中文帮助文件或使用手教程
- 关于Blob类型的数据存储,在线急等
- 没有主键的表,如何删除里面重复的数据?求助SQL?
- 歧义存储过程看不懂
- 有关ORACLE的汉字拼音问题
- 如何在水晶报表中设置数据源为Oracle的存储过程?
- 有那位知道。。。。。。。?好东西,哈哈
- 多重IF的SQL怎么写呀
- 如何利用vb编程从网络数据库中读数据?
test_target表中的数据是3000多条,test_target表中的是2500万条试一下:
....
where t1.pay_detail_id in --变更
(select t5.pay_detail_id
from test_source t4,test_target t5
where t4.company_id = 6070000
and t4.company_id = t5.company_id
and t4.id = t5.id
and t4.check_date_id = t5.check_date_id
and t4.relationid = t5.relationid
and t4.dept_id = t5.dept_id
and t4.sub_id = t5.sub_id
--and t5.pay_detail_id = t1.pay_detail_id --删除
)
update test_target t1
set (t1.togetvalues,t1.algetvalues,t1.nogetvalue) =
(select t2.togetvalues,t2.algetvalues,t2.nogetvalue
from test_source t2
where t2.company_id = 6070000
and t2.company_id = t1.company_id
and t2.id = t1.id
and t2.check_date_id = t1.check_date_id
and t2.relationid = t1.relationid
and t2.dept_id = t1.dept_id
and t2.sub_id = t1.sub_id
and t2.pay_detail_id = t1.pay_detail_id)
where exists
(select 1
from test_source t2
where t2.company_id = 6070000
and t2.company_id = t1.company_id
and t2.id = t1.id
and t2.check_date_id = t1.check_date_id
and t2.relationid = t1.relationid
and t2.dept_id = t1.dept_id
and t2.sub_id = t1.sub_id
and t2.pay_detail_id = t1.pay_detail_id)