Best way to update list of rows in oracle database

oracleperformance

I need to update a variable list of database rows. The list of the affected ids will be generated by an application, the statement will be executed about 100 times a day (with an always different collection of ids)

The first approach to update the table was:

UPDATE mytable set mycolumn = 'X' where id in (1,2,3,4 .... 2345);

I know that this statement cannot be effectively cached in the Oracle SQL area since it does not use bind variables. And if the application needs to update very frequently, it could push other statements out of the SQL area, which would have an performance impact.

Another approach could be a series of update statements 

UPDATE mytable set mycolumn = 'X' where id = 1;
UPDATE mytable set mycolumn = 'X' where id = 2;
UPDATE mytable set mycolumn = 'X' where id = 3;
UPDATE mytable set mycolumn = 'X' where id = 4;
....
UPDATE mytable set mycolumn = 'X' where id = 2345;

This approach can be cached in SQL Area as 'UPDATE mytable set mycolumn = :1 where id = :2', but it might become a problem concerning the network traffic.

So – which approach should I select?

Best Answer

Here's one idea that may or may not fit your needs. Create, say, 3 statements:

s1: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 100 ?
s2: UPDATE mytable set mycolumn = ? where id in (?, ?, ...,?) -- 10 ?
s3: UPDATE mytable set mycolumn = ? where id = ? -- 1 ?

As long as you have more than 100 parameters unprocessed, use s1. When there are 10-99, use s2, and for the remaining ones use s3. This can be thought of as a compromise between the number of statements that have to be cached in SQL area, and the number of trips to the database. If we have 963 parameters that mean that 3 statements will be cached, and there will be 9+6+3=18 updates sent over to the database.

Many host languages also have some kind of batchexecute so that you pass an array of statements, if that exists in your host language the code is likely to be cleaner than what I suggested above.

Related Question