---
title: "ORACLE 两表更新 update"
author: "winzenghua"
date: "2009-09-08T18:35:00Z"
tags: []
slug: "oracle-update"
draft: false
lastmod: "2009-09-08T18:35:00Z"
id: 987853
url: "https://java4gis.iteye.com/blog/987853"
---

oralce 的两个表关联更新 写法和另类 与MS SQL很不一样

有时候更新大表速度特慢 5－10个小时都没结束

UPDATE T\_BASE\_SUCC\_PROUSER X SET X.F\_EXPECTENDDATE = ( SELECT E.F\_ENDTIME FROM T\_GATHER\_EXPECT E WHERE X.F\_LOTID=E.F\_LOTTERYID AND X.F\_EXPECT=E.F\_EXPECT )  
T\_BASE\_SUCC\_PROUSER 昨天更新了14个小时都没解释  
今天加个索引也跑那么慢  
首次加载时间 2009-9-8 10:38:5 到现在15：58分近五个小时了  
而且EM上看IO量维持很低  
表大小7205M 行7758，5575  
执行计划  
操作 对象 对象类型 顺序 行 大小 (KB) 成本 时间 (秒) CPU 成本 I/O 成本  
TABLE ACCESS FULL T\_BASE\_SUCC\_PROUSER TABLE 1 77585575 1,439,576.099 169053 2029 36788100922 164759  
TABLE ACCESS FULL T\_GATHER\_EXPECT TABLE 2 1 0.021 139 2 25736801 136

想这样更新大表 而且IO利用率那么低 各位如何写这样更新语句啊

1

UPDATE T\_BASE\_SUCC\_PROUSER X SET X.F\_EXPECTENDDATE = ( SELECT E.F\_ENDTIME FROM T\_GATHER\_EXPECT E WHERE X.F\_LOTID=E.F\_LOTTERYID AND X.F\_EXPECT=E.F\_EXPECT )  
where exists(select 1 from T\_GATHER\_EXPECT E WHERE X.F\_LOTID=E.F\_LOTTERYID AND X.F\_EXPECT=E.F\_EXPECT)

后面一定要加上where exists(select 1 from T\_GATHER\_EXPECT E WHERE X.F\_LOTID=E.F\_LOTTERYID AND X.F\_EXPECT=E.F\_EXPECT) ，不能的话所以的数据行都将修改，当然每行只是修改一次。

2

建议用Update视图的方式来做。  
update (select x.F\_EXPECTENDDATE,E.F\_ENDTIME  
from T\_BASE\_SUCC\_PROUSER X left join T\_GATHER\_EXPECT E on X.F\_LOTID=E.F\_LOTTERYID AND X.F\_EXPECT=E.F\_EXPECT  
)  
set F\_EXPECTENDDATE=F\_ENDTIME

两表需要外键关链

3 临时表法

create table a as

select x.\*,e.f\_endtime

from x

inner join e on x.f\_lotid=e.f\_lotid and e.id=x.id

truncate table x

insert into x select * from a;