Comparison between four methods for batch ORACLE update and four methods for oracle update

Comparison between four methods for batch ORACLE update and four methods for oracle update

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