"Oracle Batch Update" compares the method of updating another large table in bulk with one large table

The "problem" now has two tens other structures of the same data in different data tables T_sms_phoneno (destination table), T_sms_phoneno2 (source table), updating the data of the destination table based on the source table data.

"Analysis" based on experience, the Update method generally has the following types:

1, direct update.

Update T_sms_phoneno T Set t.name= (select NAME from T_sms_phoneno2 where Phoneno=t.phoneno)

2, the use of sub-article update. Updated by record ID.

Open cur is select phoneno,name from T_sms_phoneno;

Loop

Fetch cur in v_pn,v_name;

Update T_sms_phoneno T set t.name=v_name where V_pn=t.phoneno;

Exit when cur% NotFound;

End Loop

3, the use of batch update. The data is updated in batches by the table partitioning field or other fields in turn.

Open cur is select region from T_sms_phoneno Group by region;

Loop

Fetch cur in v_region;

Update T_sms_phoneno T set t.name= (select NAME from T_sms_phoneno2 where Phoneno=t.phoneno) where t.region=v_region;

Exit when cur% NotFound;

End Loop

4, virtual a table, the only association after the update.

Update (select T.name,t2.name name2 from T_sms_phoneno T,t_sms_phoneno2 T2 where T.phoneno=t2.phoneno) set name=name2;

5. Create a new table and update it.

CREATE TABLE Temp_sms_phoneno as SELECT * from T_sms_phoneno T,t_sms_phoneno2 T2 where T.phoneno=t2.phoneno;

Generate a new table, and then rename the table to T_sms_phoneno

Conclusion

1, for large table update if the use of Direct UPDATE statement, it will directly produce a large number of rollback, and the time is very long, there is a phenomenon of suspended animation.

2, 1 hours.

3, 30 minutes.

4, 2 minutes.

5, 30 seconds, the INSERT statement will be very fast because it does not produce a redo,undo action.

If there is a unique associated field, then the fourth method is updated quickly, if there is no unique associated field, it needs to be updated in batches, or create a new table.

"Oracle Batch Update" compares the method of updating another large table in bulk with one large table