CODEWITHSUNDEEP
sqloracle-databaseperformance
clementino
clementino

Reputation: 327

Oracle UPDATE statement performance

The question is, which of the two is more performant in case of many (>1000) ids and why?

1) This one executed one by one for each id, and commit only after the last:

UPDATE User u SET u.status='ACTIVE' WHERE u.id=1;

2) Or:

UPDATE User u SET u.status='ACTIVE' WHERE u.id IN (1,2,3....)

Upvotes: 1

Views: 87

Answers (2)

3sky
3sky

Reputation: 890

There is no difference between those two statements, and the optimiser will transform the IN to the = for each ID. But You can always check it by Execution Plans. Generating and Displaying Execution Plans

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

The answer is that the second case should outperform the first. By how much depends on how you are running these update statements. If you plan to run them from a client, e.g. a Java program, then your first approach would incur a big latency penalty, as you keep opening new database connections for each update. If just running separate updates on Oracle directly, you might not see much difference in performance if the list of id's in the second case are reasonably small.

Upvotes: 4

Related Questions