批量update（批量更新）.docVIP

批量update（批量更新） Batch update, data method, compare test Previous / next 2011-01-24 16:04:05 / personal category: SQL tuning View (1665) / comment (4) / score (5 / 0) In many cases, we will encounter a batch of large amounts of data to update the requirements below, I will come in ROWID way (this should be the quickest way to compare the performance differences between), several processing methods, the performance of the machine (PC machine) and time reasons, here only 460W data was prepared. Comparison of various schemes of circulating processing time, comparison results. SQL select count (1) from testA; COUNT (1) Dear sirsre Four million six hundred and forty-three thousand and six hundred SQL set timing on In the first case, the unordered ROWID cursor is updated cyclically: SQL declare Cursor upd_cursor is Select, ROWID, as, row_id, from, testA; Rowd varchar (30); V_counter number; Begin Open upd_cursor; Loop Fetch upd_cursor Into rowd; Exit when upd_cursor%notfound; Execute, immediate,update, testA, set, flag=: F, where, rowid=: R Using 1, rowd; If (v_counter = 1000 then) Commit; V_counter: = 0; End if; End loop; Close upd_cursor; Commit; End; / The PL/SQL process has been successfully completed. Time taken: 00: 05: 46.78 In the second case, ordered ROWID cursor loops are updated: SQL declare Cursor upd_cursor is Select, ROWID, as, row_id, from, testA, order, by, rowid; Rowd varchar (30); V_counter number; Begin Open upd_cursor; Loop Fetch upd_cursor Into rowd; Exit when upd_cursor%notfound; Execute, immediate,update, testA, set, flag=: F, where, rowid=: R Using 1, rowd; If (v_counter = 1000 then) Commit; V_counter: = 0; End if; End loop; Close upd_cursor; Commit; End; / The PL/SQL process has been successfully completed. Time taken: 00: 05: 30.03 In the third case, the unordered ROWID bulk collect is updated: SQL declare Type, objecttab, is, table, of, VARCHAR2 (20); Rowd objecttab; Cursor upd_cursor is Select, ROWID, as, row_id, from, testA; V_counter number;