How to improve the efficiency of Oracle big data table Update

In Oracle, if the table data volume is large (M-level or larger), updating a field is very slow (for example, updating the historical business process table in my HIS project, 1.6 million records, use CURSOR to update, 1000 COMMIT once, it took four days to complete the update), and later tried to improve:

1. Cancel LOGGING on the table

2. Cancel the INDEX of the table

But it is still very slow, but you can find this:

Http://asktom.oracle.com/pls/asktom/f? P = 100: 11: 0: P11_QUESTION_ID: 6407993912330

In this Q & A question, ORA officially proposed a solution:

1. CREATE table as select xxxxx to generate a new table T1

2. Create the same index as the target table on T1

3. Delete the target table or RENAME (pay attention to backup for regret)

4. Rename T1 as the target table

I tried it and it was very fast. My task was almost completed in 2 minutes.

For example, csywdk. table_room is a large table. You need to delete the record of bakfwid in noNewYWFW20081205 and update ROOM_LOC recorded by bakfwid in imp_table_room to imp_table_room.room_loc:

(1) create a new table

Create table tmp_new_table_room081205

Select t1.ROOM _ ID, t1.NEWROOMID, t1.BUILDID, t1.TFH, t1.DKH, t1.BUILD _ NO, t1.LAYER _ NO, t1.ROOM _ NO, t1.ROOM _ NAME,

Decode (t2.bakfwid, null, t1.ROOM _ LOC, t2.room _ loc)

T1.ROOM _ AREA,

T1.SURTYPE, t1.LAYER _ NAME, t1.DEVDEP, t1.CELL, t1.DELFLAG, t1.QXXZ, t1.SJSJLSH, t1.FD, t1.ID, t1.BAKFWID

From csywdk. table_room t1 left join imp_table_room t2 on t1.bakfwid = t2.bakfwid

Where not exists (select 1 from noNewYWFW20081205 t3 where t3.bakfwid = t1.bakfwid)

(2) create a backup table

Create table Table_room081205

Select * from csywdk. table_room

(3) Replace the original table

Drop table sde. table_room

Create table sde. table_room

Select * from tmp_new_table_room081205

In this Q & amp; A; PL/SQL efficiency; is also mentioned:

"Tasks that can be processed with one statement must not be implemented by multiple programming statements ".

When I was always afraid of executing a sentence, the rollback segment was not big enough. It seems that I had to prepare the hard disk as the best strategy.