Batch update Optimization for Oracle 10g large tables

Batch update Optimization for Oracle 10g large tables. In fact, 7 million of the tables are not big tables and are enough for testing.

I. t table information
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

2. cache t data first to compare benchmark consistency
Update t set t. is_del = 0;

3. Four methods are summarized here.
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 maid 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

Through the test results above, we can see that method 3 is the best, method 0 is not recommended, this will make the undo rapid growth, there is a ora-01555 error. Methods 1 and 2 have seen tests in some books, saying that method 2 is better than method 1, but this test is not very effective and will be tested later.

Note:
The flash back feature is configured for the tested database. db_recovery_file_dest_size = 2g. Archive logs are stored in the db_recovery_file_dest directory. At the beginning of the update operation, redo is growing fast, often hang, alert log reports insufficient space, all added db_recovery_file_dest_size = 4g. Pay attention to the monitoring of undo tablespace.