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?

  • Network traffic problem? How many Mb of SQL statements are you going to send? It is about 64 bytes per SQL so this will not be a problem unless you have a very bad network. – Marco Nov 26 '15 at 15:33
  • OK, maybe 'network traffic' is the wrong aspect. Is there another aspect to consider when performing a series of statements? – ABX Nov 26 '15 at 15:37
  • Databases are meant to process lots of SQL. Do not see any problem. – Marco Nov 26 '15 at 15:42
  • If you are using JDBC, use the second approach together with statement batching. – a_horse_with_no_name Nov 26 '15 at 16:05
  • @Marco, the problem is not the amount of data but rather that each statement has to wait for the previous one to finish. If the RTT is 5ms and you execute 1000 queries it will take 5 seconds before the last one is finished. – Lennart Dec 26 '17 at 15:07

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.

| improve this answer | |

I would go for the second approach. Like you said it can be cached. When a database is much used it will process a hundreds of SQL per second. Do not think that these SQLs will badly influence the overall performance of your database.

The only 'problem' you can get is that you update too many rows and keep to many locks. Do a commit every, let's say, 1000 updates to keep the risk of blocking locks small.

| improve this answer | |
  • 1
    Recommending frequent commits is not a good advice. What if the everything needs to be rolled back? Also locks are not expensive in Oracle. There is nothing wrong with that. And they will not cause "blocking" because those rows can still be read by other transactions. – a_horse_with_no_name Nov 26 '15 at 16:06
  • Check articles about processing a resultset in PL/SQL. You see most of the times a commit after 1000 or more rows. Yes you can read the rows that are locked but you cannot be updated them. – Marco Nov 26 '15 at 17:43
  • 1
    A transaction should be committed when it's finished, not earlier and/or in arbitrary number of rows. Marco, I suggest you search the official Oracle advice on this (AskTom). They agree with @a_horse_with_no_name. – ypercubeᵀᴹ Nov 26 '15 at 18:03

Series of update statements as you you posted in the question will be hard parsed unless you set cursor_sharing parameter to force or similar, so you will end up having thousands versions of the same query in SGA (even worse than the first approach with just one query with WHERE IN (1,2,3,...).

You need to use bind variables ,the query on client-side should look like UPDATE schema_name.mytable set mycolumn = :1 where id = :2"; You can also use modified approach #1 , split the list of ids, for instance, into buckets with 5 elements. Then you can run update ... where id IN (:1, :2, :3, :4, :5) in the loop. For example, array with 6 elements , 1,2,3,4,5,6 is split into 2 : 1st :1,2,3,4,5 and 2nd : 6,NULL,NULL,NULL,NULL.

| improve this answer | |

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;

Rather than updating the table directly, I suggest staging the ids in a global temporary table. This is likely to be faster, especially in the case where the table you are inserting into has indexes, as you are giving the optimiser freedom to chose how to order the inserts. You could use @Lennart's excellent suggestion for doing that with a small number of statements with varying numbers of bind variables, but here is the general idea:

create table mytable(id integer, mycolumn char(1) default 'O');
insert into mytable(id) select level from dual connect by level<10;
create global temporary table ids_to_update(id integer) on commit delete rows;
insert into ids_to_update(id) values(1);
insert into ids_to_update(id) values(2);
insert into ids_to_update(id) values(3);
update mytable set mycolumn='X' where id in(select id from ids_to_update);
select * from mytable;
-: | :-------
 1 | X       
 2 | X       
 3 | X       
 4 | O       
 5 | O       
 6 | O       
 7 | O       
 8 | O       
 9 | O       

dbfiddle here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.