update的优化

在olap中，常常能看到性状很差的语句是update语句，跑半天都跑不过去，虽然语句可以千变万化，但是优化起来还是有规可循的。

--测试表：
drop table t1;
drop table t2;
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;


--原始update语句
update t1 set t1.owner=(select t2.owner from t2 where t2.object_id=t1.object_id);

--683s

rollback;

履行筹划以下：
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      | 69746 |  2043K|   150   (1)| 00:00:03 |
|   1 |  UPDATE            | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 69746 |  2043K|   150   (1)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| T2   |   546 | 16380 |   150   (1)| 00:00:03 |
---------------------------------------------------------------------------


两个小表竟然花了10多分钟，至于为何这么慢，我就不说了，要研讨的话可以看下语句真实的履行筹划，请看《如何获得履行筹划》这篇文章，我只说一下优化的办法。


--1树立组合索引
create index idx on t2(object_id,owner);
update t1 set t1.owner=(select t2.owner from t2 where t2.object_id=t1.object_id);

--0.7s

rollback;
由于t2只用到了两个字段的数据，object_id和owner，斟酌将起树立组合索引，扫描的时候只须要扫描索引中的数据便可
只能用object_id，owner的次序能力让索引走range scan进步效力，owner，object_id的次序是错的。
--2plsql分批update
declare
v_count number;
cursor c is
select t1.rowid row_id,t2.object_id,t2.owner from t1,t2 where t1.object_id=t2.object_id;
begin
  v_count:=0;
  for x in c loop
    update t1 set t1.owner=x.owner where rowid=x.row_id;
    v_count:=v_count+1;	
    if (v_count>=1000) then
      commit;
      v_count:=0;
    end if;
  end loop;
  commit;
end;

--1.9s
通过rowid定位update的数据，避免每次update都走全表扫描。
--3merger into优化（undo较多，怕逝世事务恢复）
merge into  t1
using  t2
on (t1.object_id=t2.object_id)
when matched then
  update set t1.owner=t2.owner;

--0.84s
  
  
  总结：
直接update大表是最垃圾的写法。
办法1：当表较小时，效力较高。可以这样用。当表大时，频繁扫描索引，会发生热门块，会发生锁期待：cbc latch。不推举。
办法2：当表大时，推举用这类办法，分批提交，避免大事务。不然大事务一直不提交占用回滚段，容易报回滚段不足的错。这也是为何有时候跑数跑不过，有时候又没问题的基本缘由。不是oracle的问题，是语句的问题。
办法3：如果你用set autotrace on的办法测试，你会发明merge发生的undo是非常多的。一旦断电或其他缘由造成数据库down机，那末就完了。。。数据库为了保证数据的一致性，启动以后要读undo进行恢复，读undo是单块读，非常慢，如果多块读参数为16，你merge了1个小时还没完成，突然down机了，那末恢复起来就要16个小时能力恢复完，数据库16个小时不能工作那就坑爹了。。。


在olap中，常常能看到性状很差的语句是update语句，跑半天都跑不过去，虽然语句可以千变万化，但