Comparison of Oracle batch update Methods

Software Environment Windows 2000 + Oracle9i
Hardware environment CPU 1.8G + RAM 512 M
 
Now we have two tables:
T1 -- large table 10000 T1_FK_ID
T2 -- small table 5000 T2_PK_ID
T1 is associated with the primary key ID of T2.
 
The simulation data is as follows:
-- T2 contains 5000 pieces of data
Create table T2
As
Select rownum id, .*
From all_objects
Where 1 = 0;
 
-- Create/Recreate primary, unique and foreign key constraints
Alter table T2
Add constraint T2_PK_ID primary key (ID );
 
Insert/* + APPEND */into T2
Select rownum id, .*
From all_objects a where rownum <= 5000;

-- T1 has 10000 pieces of data
Create table T1
As
Select rownum sid, T2 .*
From T2
Where 1 = 0;
 
-- Create/Recreate primary, unique and foreign key constraints
Alter table T1
Add constraint T1_FK_ID foreign key (ID)
References t2 (ID );
 
Insert/* + APPEND */into T1
Select rownum sid, T2 .*
From T2;
 
Insert/* + APPEND */into T1
Select rownum sid, T2 .*
From T2;
 
-- Update the Subobject_Name field, which is null before.
Update T2 set T2.Subobject _ Name = 'steven Huang'
 
We hope to update all the Subobject_Name fields of T1 into 'gradhuang', that is, 10000 pieces of data of T1 will be updated.
 
Method 1
Write PL/SQL, open cursor
Declare
Rochelle varid varchar2 (20 );
Rochelle varsubname varchar2 (30 );
Cursor mycur is select T2.Id, T2.Subobject _ Name from T2;
 
Begin
Open mycur;
Loop
Fetch mycur into l_varID, l_varSubName;
Exit when mycur % notfound;
Update T1 set T1.Subobject _ Name = l_varSubName where T1.ID = l_varID;
End loop;
Close mycur;
End;
--- Time consumed: 39.716 s
Obviously, this is the most traditional method. If the data volume is large (40 million), a "snapshot too old" error will be reported to exit.
 
Method 2.
Loop and batch operation
Declare
I number;
J number;
Begin
I: = 1;
J: = 0;
Select count (*) into j from T1;
Loop
Exit when I> j;
Update T1 set T1.Subobject _ Name = (select T2.Subobject _ Name from T2 where T1.ID = T2.ID)
Where T1.ID> = I and T1.ID <= (I + 1000 );
I: = I + 1000;
End loop;
End;
-- It takes 0.656 s, and a total of 10 loops are made here. If the data volume is large, although the task can be completed, the speed is still unsatisfactory. (For example, we increase T1-large tables to 100000 T2.-small tables to 50000
) The time consumed is 10.139 s.
 
Method 3.
-- Virtualize a table to perform operations, which is much more efficient than method 2 in the case of a large amount of data
Update (select T1.Subobject _ Name a1, T2.Subobject _ Name b1 from T1, T2 where T1.ID = T2.ID)
Set a1 = b1;
-- Takes 3.234 s (T1 -- the size of a large table increases to 100000 T2. -- the size of a small table increases to 50000)
 
Method 4.
-- Because UPDATE is a resource-consuming operation, there will be redo and undo operations. In this example, we can use the following method to create a new table, because insert is much faster than update, after that, you will have an old table and a new table. Then, you can analyze the specific situation as needed ~~~~~
Create table T3 as select * from T1 where rownum <1;
Alter table T3 nologging;
Insert/* + APPEND */into T3
Select T1. * from T1, T2 where T1.ID = T2.ID;
-- Takes 0.398 s (T1 -- the size of a large table increases to 100000 T2. -- the size of a small table increases to 50000)
 
* All the operations above have excluded the time required to analyze the execution plan