批量更新，MERGE语句性能最好，因为它可以多块读，并且可以并行执行，但是缺点就是消耗比较多的UNDO，一旦down机死事物恢复较慢。 ORDER BY ROWID 在 buffer cache 不够大的情况下性能较好好(没Merge快，因为Merge可以多块读，走ROWID只能单块读)。 优点就是可以批量提交。缺点就是不能并行更新。 不 ORDER BY ROWID 在 buffer cache足够大(能确保被更新的表不被page out) 的情况下性能较好。 create table a as select * from dba_objects; create table b as select * from dba_objects; insert into b select * from b; --- 直到插入60W数据 sql> SELECT COUNT(*) FROM B; COUNT(*) ---------- 616864 sql> SELECT SUM(BYTES)/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME='B'; SIZE(MB) ---------- 72 create index idx_a on a(object_name,object_id); create index idx_b on b(object_id); 比如要执行这个update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id); 可以用MERGE代替 -------------------------MERGE版本,使用MERGE一定要确保MERGE into 的表走全表扫描---------------- alter session set db_file_multiblock_read_count=128; 如果要更新的表很大，alter session enable parallel dml; alter session set workarea_size_policy=manual; alter session set sort_area_size=xxx; alter session set hash_area_size=xxx; merge /*+ USE_HASH(C,H) FULL(C) */ into b c using (select /*+INDEX(A) USE_HASH(A) */ a.object_name,a.object_id from a where a.object_id in (select /*+ use_hash(b) index(b) */ object_id from b)) h on (c.object_id = h.object_id) when matched then update set c.object_name = h.object_name; select * from table(dbms_xplan.display); 也可以写PL/sql -------------------------PL/sql版本--------------------------------- DECLARE CURSOR CUR_B IS SELECT a.object_id,a.object_name,b.ROWID ROW_ID FROM A,B WHERE A.object_id = B.object_id ORDER BY B.ROWID; V_COUNTER NUMBER; BEGIN V_COUNTER := 0; FOR ROW_B IN CUR_B LOOP UPDATE b SET object_name = ROW_B.object_name WHERE ROWID = ROW_B.ROW_ID; V_COUNTER := V_COUNTER + 1; IF (V_COUNTER >= 10000) THEN COMMIT; dbms_output.put_line('Updated: ' ||V_COUNTER || ' lines.'); V_COUNTER := 0; END IF; END LOOP; COMMIT; END; / 下面这个版本是批量处理的版本 ------------------------批量处理版本-------------------------------- declare maxrows number default 100000; row_id_table dbms_sql.urowid_table; --currcount_table dbms_sql.number_Table; object_name_table dbms_sql.varchar2_Table; cursor cur_b is SELECT /*+ index(a) use_hash(a,b) index(b) */ a.object_name,B WHERE A.object_id = B.object_id ORDER BY B.ROWID; v_counter number; begin v_counter := 0; open cur_b; loop EXIT WHEN cur_b%NOTFOUND; FETCH cur_b bulk collect into object_name_table,row_id_table limit maxrows; forall i in 1 .. row_id_table.count update b set object_name = object_name_table(i) where rowid = row_id_table(i); commit; end loop; end; / 关于ORDER BY ROWID提升速度的验证 -------------------Buffer cache 不够大--------------------------------------- buffer cache 40Mb，B表有72Mb 所以不够存放下 B keep pool 52Mb--keep idx_a,idx_b sql> alter system flush buffer_cache; 系统已更改。 已用时间: 00: 00: 00.00 sql> merge into b c 2 using (select a.object_name,a.object_id 3 from a 4 where a.object_id in (select object_id from b)) h 5 on (c.object_id = h.object_id) 6 when matched then 7 update set c.object_name = h.object_name; 616851 行已合并。 已用时间: 00: 00: 12.51 sql> alter system flush buffer_cache; 系统已更改。 已用时间: 00: 00: 00.00 sql> declare 2 maxrows number default 100000; 3 row_id_table dbms_sql.urowid_table; 4 --currcount_table dbms_sql.number_Table; 5 object_name_table dbms_sql.varchar2_Table; 6 cursor cur_b is 7 SELECT /*+ index(a) use_hash(a,b) index(b) */ 8 a.object_name,b.ROWID ROW_ID 9 FROM A,B 10 WHERE A.object_id = B.object_id 11 ORDER BY B.ROWID; ------有ORDER BY ROWID 12 v_counter number; 13 begin 14 v_counter := 0; 15 open cur_b; 16 loop 17 EXIT WHEN cur_b%NOTFOUND; 18 FETCH cur_b bulk collect 19 into object_name_table,row_id_table limit maxrows; 20 forall i in 1 .. row_id_table.count 21 update b 22 set object_name = object_name_table(i) 23 where rowid = row_id_table(i); 24 commit; 25 end loop; 26 end; 27 / PL/sql 过程已成功完成。 已用时间: 00: 00: 31.71 sql> alter system flush buffer_cache; 系统已更改。 已用时间: 00: 00: 01.87 sql> declare 2 maxrows number default 100000; 3 row_id_table dbms_sql.urowid_table; 4 --currcount_table dbms_sql.number_Table; 5 object_name_table dbms_sql.varchar2_Table; 6 cursor cur_b is 7 SELECT /*+ index(a) use_hash(a,B 10 WHERE A.object_id = B.object_id; 11 v_counter number; 12 begin 13 v_counter := 0; 14 open cur_b; 15 loop 16 EXIT WHEN cur_b%NOTFOUND; 17 FETCH cur_b bulk collect 18 into object_name_table,row_id_table limit maxrows; 19 forall i in 1 .. row_id_table.count 20 update b 21 set object_name = object_name_table(i) 22 where rowid = row_id_table(i); 23 commit; 24 end loop; 25 end; 26 / PL/sql 过程已成功完成。 已用时间: 00: 01: 25.64 MERGE只需要13秒，ORDER BY ROWID 的PL/sql 需要32秒，而没ORDER BY ROWID 的PL/sql 需要1分26秒 如果buffer cache不够大(不能容纳下A,B)，不order by rowid 要花1分25秒，order by rowid只花了32秒 可见，ORDER BY ROWID 在BUFFER CACHE不够大的情况下，对于速度的提升是非常明显的，因为buffer cache不够大，block可能经常被page out。 order by rowid 会连续更新临近的block,这样就确保读入的block尽可能的不被page out。 为什么Merge 比 用ROWID 去更新快呢？因为MERGE可以多块读，做MERGE的时候设置参数db_file_multiblock_read_count=128 根据ROWID去更新，只能一次读一个block ---------------------BUFFER CACHE 足够大，多次执行，取运行最快的时间--------------------------------------- sql> alter system set db_keep_cache_size=150m; 系统已更改。 sql> alter table a storage(buffer_pool keep); 表已更改。 sql> alter table b storage(buffer_pool keep); 表已更改。 sql> select /*+ full(a) */ count(*) from a; COUNT(*) ---------- 50526 已用时间: 00: 00: 00.16 sql> select /*+ full(b) */ count(*) from b; COUNT(*) ---------- 616864 已用时间: 00: 00: 01.08 sql> SELECT o.OWNER,o.object_type,o.OBJECT_NAME,COUNT(*) NUMBER_OF_BLOCKS 2 FROM DBA_OBJECTS o,V$BH bh 3 WHERE o.DATA_OBJECT_ID = bh.OBJD 4 AND o.owner='SCOTT' 5 GROUP BY o.owner,o.OBJECT_NAME 6 ORDER BY COUNT(*)desc,2 ; OWNER OBJECT_TYPE OBJECT_NAME NUMBER_OF_BLOCKS ------------------------------ ------------------- ------------------------------ ---------------- SCOTT TABLE B 8467 SCOTT INDEX IDX_B 1394 SCOTT TABLE A 695 SCOTT INDEX IDX_A 288 sql> declare 2 maxrows number default 100000; 3 row_id_table dbms_sql.urowid_table; 4 --currcount_table dbms_sql.number_Table; 5 object_name_table dbms_sql.varchar2_Table; 6 cursor cur_b is 7 SELECT /*+ index(a) use_hash(a,B 10 WHERE A.object_id = B.object_id 11 ORDER BY B.ROWID; 12 v_counter number; 13 begin 14 v_counter := 0; 15 open cur_b; 16 loop 17 EXIT WHEN cur_b%NOTFOUND; 18 FETCH cur_b bulk collect 19 into object_name_table,row_id_table limit maxrows; 20 forall i in 1 .. row_id_table.count 21 update b 22 set object_name = object_name_table(i) 23 where rowid = row_id_table(i); 24 commit; 25 end loop; 26 end; 27 / PL/sql 过程已成功完成。 已用时间: 00: 00: 11.83 sql> declare 2 maxrows number default 100000; 3 row_id_table dbms_sql.urowid_table; 4 --currcount_table dbms_sql.number_Table; 5 object_name_table dbms_sql.varchar2_Table; 6 cursor cur_b is 7 SELECT /*+ index(a) use_hash(a,row_id_table limit maxrows; 19 forall i in 1 .. row_id_table.count 20 update b 21 set object_name = object_name_table(i) 22 where rowid = row_id_table(i); 23 commit; 24 end loop; 25 end; 26 / PL/sql 过程已成功完成。 已用时间: 00: 00: 09.71 sql> merge into b c 2 using (select a.object_name,a.object_id 3 from a 4 where a.object_id in (select object_id from b)) h 5 on (c.object_id = h.object_id) 6 when matched then 7 update set c.object_name = h.object_name; 616851 行已合并。 已用时间: 00: 00: 08.54 ORDER BY ROWID 的PL/sql 用了11秒，没有ORDER BY ROWID的PL/sql用了9秒，而Merge最快，只花了8秒多。 (反复测试，以最快时间为准，添加了3个logfile group 每组500Mb,减少logfile对测试的影响)。 由此可见，如果buffer cache够大，不order by rowid 反比order by rowid更快(因为少了排序)
大数据updata ----转帖请注明作者和出处: fromeast http://www.itpub.net/thread-1052077-1-1.html 最近一直在折腾大表的更新问题，今天终于有了突破。兴奋之余发个帖子跟大家分享一下心得，并且讨论一下是否还可能进一步提高处理速度。 问题是这样的：一张5亿条记录的表，没有分区。由于增加了一个冗余字段，需要根据另外一张表（4.8亿条）更新这个大表。下面是具体的描述： 环境：HP-UX 11i+Oracle9.2.0.8+RAID 要更新的表：T1 (id1 number,id2 number,curr_count number,.....) --id1唯一 5亿条记录 >60GB 更新数据来源：T2 (id2 number,curr_count number) --id2唯一 4.8亿 更新逻辑：T2中的每一条记录，都到T1中找到对应的记录（T2.id2=T1.id2），更新T1.curr_count=T2.curr_count 限制条件：只能在线更新（应用程序一直在访问这个表，所以不能用INSERT SELECT），不能占用太多系统资源，要求3天之内更新完毕。 原来的做法： declare cursor cur_t2 is select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */ T2.id2,T2.curr_count,T1.rowid row_id from T1,T2 where T1.id2=T2.id2; v_counter number; begin v_counter := 0; for row_t2 in cur_t2 loop update T1 set curr_count=row_t2.curr_count where rowid=row_t2.row_id; v_counter := v_counter + 1; if (v_counter>=1000) then commit; v_counter := 0; end if; end loop; commit; end; / 问题：更新太慢，260 rows/s，全部更新完毕需要22天！ 经过调查发现是UPDATE语句执行的效率太低，进一步的跟踪发现，UPDATE至少90%的时间是在等待db file sequential read这个事件。按说都ROWID了，为什么还有这么多磁盘等待？再看disk reads，明白了，原来UPDATE语句产生了大量的物理读，当然慢了。想必T1表太大了，Data Buffer装不下，并且有其他的表跟它竞争，所以刚更新一条数据，从磁盘读取了一个数据块到内存，很快就被挤去出了，下次更新这个块上的其他数据时，还得再从磁盘读取。这样Data Buffer Cache的效率就很低，基本没有利用上。 怎么解决呢？最好是能按数据块的顺序更新，这样某个数据块里的第一行数据更新后，数据块内的其他行就不用再从磁盘里读取了（不太可能那么快就被挤出内存），物理读降低了，速度肯定能加快。可是怎样按数据块的顺序更新呢？我想到了ROWID的结构是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，那么ROWID的顺序应该就是数据块的顺序了。于是我修改了PLsql： alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB declare cursor cur_t2 is select /*+ use_hash(T1,T2 where T1.id2=T2.id2 order by T1.rowid; v_counter number; begin v_counter := 0; for row_t2 in cur_t2 loop update T1 set curr_count=row_t2.curr_count where rowid=row_t2.row_id; v_counter := v_counter + 1; if (v_counter>=1000) then commit; v_counter := 0; end if; end loop; commit; end; / alter table T1 storage(buffer_pool default); 这回更新的速度大为加快：10000 rows/s。分析跟踪文件表明db file sequential reads和磁盘读取变的很少。按照这个速度20个小时之内就能全部更新完了。 心得：处理的数据量并没有减少，只是改变一下处理的顺序，也可以极大地提高性能。 ==================================================================== *后记4：已上生产 *后记3：试验了KEEP的影响 (1)重新运行试验1（不order by rowid） 开始的语句改成：alter table T1 storage(buffer_pool keep); 处理速度：73~74行/秒 (2)重新运行试验2（order by rowid）： 开始的语句改成：alter table T1 storage(buffer_pool default); 处理速度：1万条/秒 结论：从本次测试可以印证先前的推断——把表的buffer_pool属性设为keep与否，对处理速度的影响很小，以至于可以忽略。处理速度加快的原因，是因为order by rowid，按块顺序处理数据，很大程度上减少了物理读。 *后记2：关于order by rowid的资料： http://rdc.taobao.com/blog/dba/html/199_oracle_rowid_order.html 这篇文章说order by rowid导致大量的查询物理读。其实在本文第二个测试中也是这样的——CURSOR的打开时间比不ORDER BY ROWID时间要长，因为多了SORT。可是这样是值得的，因为后续有大量的UPDATE，节省的物理读是很可观的。 *后记1：修改了几处错误： where T1.id1=T2.id2 => where T1.id2=T2.id2 parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)原文链接：https://www.f2er.com/oracle/213833.html
