FOR vs FORALL

[toddyl]

Hi,

I have over 200,000 rows of data which I need to insert into a DB table on a daily basis. Some of these rows may update one row in the DB or may create new rows depending on the incoming data.

What is the best way of doing this?

Option 1:
I was thinking of using a FOR LOOP and in it I would first try and update the row, if this has a SQL%ROWCOUNT of 0 then I'll do an insert. -- this would mean looping 200,000 times.

Option 2:
I will do a FORALL and do an UPDATE for all rows, then immediately after do a FORALL with an INSERT for all rows. -- this way would mean looping 400,000 times.

Would the FORALL method still be quicker?
Is there another way of implementing this?

Cheers,

toddyl

 

[Thargy]

toddyl,

The oracle merge command does exactly what you want. If it finds a matching record, it updates, if nothing matches, it inserts. This is the so-called 'upsert' situation.

Do you want to look at this yourself first or do you want an example?

Regards

Tharg

Grinding away at things Oracular

 

[Dagon]

Although the syntax looks a bit like a loop, a FORALL is not a loop. A FORALL statement fires all the rows at the database as part of a single transaction. The key thing is not the number of times you go round a loop - it is how many individual updates you fire at the database. Using a FOR loop wolud require 400000 individual update statements to be sent to the database. Collecting the data into a large PL/SQL table and then using FORALL would only require one update.

If you use the FORALL approach, you will need to have a primary key on the table so that all the rows which already exist get rejected. You will then have to use the SQL%BULK_EXCEPTIONS table to find all the rows which were rejected and do updates to those instead. This can get quite messy.

As Tharg says, a MERGE may well be the best option. The only drawback is that it is a single transaction, so your rollback segments would have to be big enough to handle all the rows in one go. Also, if any error occurred (e.g. attempt to populate a non-null field with a null), the whole statement would crash. Row by row or FORALL update would allow you to handle individual row errors separately.