批量UPDATE的操作

有一个简单的业务需求，要根据另外一个表的ID去更新这张表的SYS为SCOTT；要更新２１１W记录。

SQL> create index t_idx1 on t_merge1(object_id);

Index created.

SQL> create index idx_t on t_merge2(object_id);

Index created.

update t_merge1 set owner='SCOTT' where object_id in(select object_id from t_merge2 where t_merge2.object_id=t_merge2.object_id);

21047392 rows updated.

Elapsed: 00:32:16.39
耗时：　３２分钟

merge into t_merge1 a 
    using (select object_id from t_merge2) b
on(a.object_id=b.object_id)
when matched then 
    update set a.owner='SCOTT';

21047392 rows merged.

Elapsed: 00:13:22.77
耗时：１３分钟

MERGE的操作大大提高了更新速度。