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.
