how to upsert an oracle table with huge amount of data?

donhoff · Post by **donhoff** » Mon Sep 24, 2007 3:59 am

Hi, I am upserting a oracle table. But the data volume is very huge.

The incremental data was 100Million records, 50% are update and 50% are insert.

Now I tried to bulk load it into a tmp table, then I used the merge sql. But the merge sql runs about 7 hours long.

Is there any way that I could upsert the data fastly?

Thanks!

ray.wurlod · Post by **ray.wurlod** » Mon Sep 24, 2007 5:56 am

That's about 4000 rows/second which, if you believe this is a valid metric, isn't all that bad.

You could try separating the operations. Use direct write (bulk load) for the inserts, then upsert (with "Update Only" as the rule) for the updates.

kwwilliams · Post by **kwwilliams** » Mon Sep 24, 2007 8:05 am

If you go to asktom.oracle.com, Mr. Kyte would tell you that instead of performing an upsert, you should be dropping and rebuilding the table using loader. 100 million row operations are going to take some time to finish, not matter which tool you may have performing them.