update的优化

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个小时不能工作那就坑爹了。。。