.jpg)
racle 10g大表批量更新優(yōu)化，其實(shí)，700萬的表不算大表，作為測(cè)試夠了 一，t表信息 SQL alter table t add is_del number(1); SQL alter table t modify is_del default 0; SQL desc t Name Type Nullable Default Comments —— ——— ——– ——-
racle 10g大表批量更新優(yōu)化，其實(shí)，700萬的表不算大表，作為測(cè)試夠了
一，t表信息
SQL> alter table t add is_del number(1);
SQL> alter table t modify is_del default 0;
SQL> desc t
Name?? Type????? Nullable Default Comments
—— ——— ——– ——- ——–
ID???? NUMBER??? Y???????????????????????
CODE?? NUMBER??? Y???????????????????????
IS_DEL NUMBER(1) Y??????? 0
SQL> select count(*) from t;
? COUNT(*)
———-
? 7136976
二，為了比較基準(zhǔn)的一致性，先緩存t數(shù)據(jù)
update t set t.is_del = 0;
三，這里共總結(jié)了4種方法
SQL> set timing on
–0
SQL> update t set t.is_del = 0;
7136976 rows updated.
Elapsed: 00:08:28.64
–1
SQL> declare
? 2??? rnt pls_integer := 0;
? 3? begin
? 4??? for idx in (select rowid rid from t) loop
? 5????? update t set t.is_del = 0 where rowid = idx.rid;
? 6????? rnt := rnt + 1;
? 7????? if mod(rnt,2000) = 0 then
? 8??????? commit;
? 9????? end if;
10??? end loop;
11??? commit;
12? end;
13? /
PL/SQL procedure successfully completed.
Elapsed: 00:09:41.32
SQL>
–2
SQL> declare
? 2??? rnt pls_integer := 0;
? 3? begin
? 4??? for idx in (select rowid rid from t) loop
? 5????? update t set t.is_del = 0 where rowid = idx.rid;
? 6????? rnt := rnt + 1;
? 7????? if rnt = 2000 then
? 8??????? rnt := 0;
? 9??????? commit;
10????? end if;
11??? end loop;
12??? commit;
13? end;
14? /
PL/SQL procedure successfully completed.
Elapsed: 00:09:35.67
–3
SQL> declare
? 2??? cursor cur_t is select rowid rid from t;
? 3??? type tab_t is table of urowid index by binary_integer;
? 4??? l_rid tab_t;
? 5? begin
? 6??? open cur_t;
? 7??? loop
? 8????? fetch cur_t bulk collect into l_rid limit 2000;
? 9????? forall idx in 1 .. l_rid.count
10??????? update t set t.is_del = 0 where rowid = l_rid(idx);
11????? commit;
12????? exit when cur_t%notfound;
13??? end loop;
14??? close cur_t;
15? end;
16? /
PL/SQL procedure successfully completed.
Elapsed: 00:06:48.84
通過上面的測(cè)試結(jié)果可以看到，方法3最好，方法0不建議使用，這會(huì)使undo快速增長(zhǎng)，出現(xiàn)ora-01555錯(cuò)誤。方法1和方法2在一些書籍上看到過測(cè)試，說方法2優(yōu)于方法1，但我這次測(cè)試效果不明顯，以后再進(jìn)行一些測(cè)試。
注：
測(cè)試的數(shù)據(jù)庫(kù)配置了閃回特性，db_recovery_file_dest_size=2g，歸檔日志放在db_recovery_file_dest目錄中。開始時(shí)的更新操作，redo增長(zhǎng)很快，常常hang住了，alert log報(bào)空間不足，所有增加了db_recovery_file_dest_size=4g。還有要注意undo表空間的監(jiān)控。
.png)
