更新个大表都超过了8个小时了

Question

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利用率那么低 各位如何写这样更新语句啊

Answer 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)

Answer 2

undo空间开大一点，undo retention也大一点(防止快照过旧失败)，批量提交。

Answer 3

where exists(select 1 from T_GATHER_EXPECT E WHERE X.F_LOTID=E.F_LOTTERYID AND X.F_EXPECT=E.F_EXPECT)这个是什么意思啊？ 不理解！ 存在于
其实 T_BASE_SUCC_PROUSER 的DATE 都是来源于T_GATHER_EXPECT 只是有些数据不一致

Answer 4

undo_retention＝900 UNDOTBS1 32G 使用了 2G

Answer 5

建议用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
;要么就用建临时表用insert和select替代update。

Answer 6

表数据量大吗
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 SELECT F_ENDTIME FROM T_GATHER_EXPECT F where  X.F_LOTID=F.F_LOTTERYID AND X.F_EXPECT=F.F_EXPECT)还有你的索引是如何建立的。

Answer 7

后面一定要加上where exists(select 1 from T_GATHER_EXPECT E WHERE X.F_LOTID=E.F_LOTTERYID AND X.F_EXPECT=E.F_EXPECT) ，不能的话，这个表的F_EXPECTENDDATE 列将都等于最后的T_GATHER_EXPECT.F_ENDTIME 。
另外，我要给lz提点建议：
     1、维护这么大的表，你需要做好以下准备:
         1)确认日志文件的空间够大；
          2）确认数据库文件已经备份；
          3）确认维护的耗时是工作中可以承受的范围
          4）对中途失败有备选方案
    2、建议做些真正维护前的演习工作：
          1）将数据库导出到新的测试数据库，
          2）将数据表分片处理，计算单位片数据的处理时间，再得到总的时间
          3）计算单位数据片产生的日志空间，再估算总体需要的空间，（日志空间可重复使用）
祝你好运 

Answer 8

ORA-01779:无法修改与非键值保存表对应的列

Answer 9

update 改为 Merge，肯定可以提高效率

Answer 10

后面一定要加上where exists(select 1 from T_GATHER_EXPECT E WHERE X.F_LOTID=E.F_LOTTERYID AND X.F_EXPECT=E.F_EXPECT) ，不能的话，这个表的F_EXPECTENDDATE 列将都等于最后的T_GATHER_EXPECT.F_ENDTIME你的意思说 不能的话 每次就更新全表所有行的日期 对吧！
T_BASE_SUCC_PROUSER 有100行 T_GATHER_EXPECT 有10行 那么将更新 100×10＝1000次！

Answer 11

不好意思，我第一话说错了，应该是：
后面一定要加上where exists(select 1 from T_GATHER_EXPECT E WHERE X.F_LOTID=E.F_LOTTERYID AND X.F_EXPECT=E.F_EXPECT) ，不能的话所以的数据行都将修改，当然每行只是修改一次。
  若（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 ）有且只有一行记录，则相应的行修改正确；  
  若有两行或以上的记录，则报错后退出；
  若没有记录，将赋予空值。

Answer 12

关闭日志看看
1. sql> alter   table   table_name   NOLOGGING; 2. update dable3. 插入完数据后，再修改表写日志： 
sql> alter   table   table_name   LOGGING; 

Answer 13

顶楼上的  改为nologging

Answer 14

如果T_GATHER_EXPECT 的F_LOTTERYID和F_EXPECT不是主键的话，请建一个唯一索引。
create unique index xxxx on T_GATHER_EXPECT(F_LOTTERYID,F_EXPECT);因为如果T_GATHER_EXPECT(F_LOTTERYID,F_EXPECT)的记录不是唯一的话，那么你的Update语句是会报错的，而且是不合理的。

Answer 15

其实update的效率比insert和select低很多。所以我建议采用中间临时表。
create table T_BASE_SUCC_PROUSER_TMP as select * from T_BASE_SUCC_PROUSER where 1=2;
insert into T_BASE_SUCC_PROUSER_TMP(...此处列举字段)
select x.字段列举,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
;
如果这步执行的时间在2个小时之内，那么就比你的update效率高了几倍。truncate table T_BASE_SUCC_PROUSER;
insert into T_BASE_SUCC_PROUSER select * from T_BASE_SUCC_PROUSER_TMP;
drop table T_BASE_SUCC_PROUSER_TMP purge;