Update large number of rows in Oracle

Question

Hi All

I have this situation and I'm trying to think of the best way to solve it. I have a database, say DB-A, with a table T-A that has 2 fields - OID and PID. This table has close to 1million rows. Now due to some other issue, the PIDs of most of the rows were incorrectly set to 0 and this was found only after a couple of days.

I have a backup from 2 days back, say DB-B, and I'm thinking of updating DB-A with matching entries from DB-B. So what would be the best way to do this ? I have this in mind:

1. Create a .NET app that gets rows from DB-B and updates matching ones in DB-A. Not sure if this will work because of the large no of rows. Maybe will need individual commits under a scoped transaction.
   1. Create a .NET to generate an Oracle SQLloader file from DB-B and load into DB-A

Your thoughts are greatly appreciated...

thanks Sunit

Answer 1

There may be a built in tool for this, but if it were me, I would probably use DB-B to generate a list of update statements and run them as a script in DB-A.

Something like:

select 'update T-A set PID = ' || PID || ' where OID = ' || OID || ';' from T-A

(This assumes that OID is your primary key. Since you have a large number of rows you might have to break it into multiple scripts. Someone else may be able to offer a more elegant solution.)

Answer 2

Can't you load the backup as (e.g.) T-B and do a query like:

UPDATE T-A, T-B SET T-A.pid = T-B.pid WHERE T-A.oid = T-B.oid

Excuse my MySQL-ese SQL, but in theory it shoulw work.

Answer 3

Hi,

If you have a 1-1 relation among DBA.TA and DBB.TB, first I would delete DBA.TA and then change Asmodon's answer UPDATE's to INSERT's.

If it's not possible I would disable/delete every constraint/index on DBA.TA, do the updates and then reenable/create the constraints/indexes.

Regards.

Answer 4

If you can build a dblink from DB-A to DB-B then you could execute a simple update from DB-A:

UPDATE (SELECT ta.pid ta_pid, tb.pid tb_pid
          FROM ta ta
          JOIN ta@backup tb ON (ta.oid = tb.oid))
   SET ta_pid = tb_pid;

It will work if OID is a primary key.

Answer 5

Reconsider the update!

It have a very high cost to do an update when is for a lot of rows. Take a look to this other answer from mine on another question.

1. Create a new table TC with a select with a JOIN on both tables TA and TB.
2. Rename TA to TX
3. Rename the result table TC to TA.