CODEWITHSUNDEEP
oracle-databaseperformance
eatSleepCode
eatSleepCode

Reputation: 4637

Oracle select in loop vs select with in clause

Which will perform faster 

1. 
SELECT * 
FROM test
WHERE id IN (1, 2, 3, 4, 5);

Or

2.
FOR s IN (SELECT id FROM ids)
LOOP
  SELECT * FROM test where id = s.id;
END LOOP;

I think the first query will perform faster since it will be executed once but looking at second query it just need to fetch data related to one id which is going to be only one row at a time, that is confusing to me.

I just need to know conceptually that which one will perform better and which is correct way?

Upvotes: 2

Views: 1095

Answers (1)

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

Tom Kyte's mantra is:

  • You should do it in a single SQL statement if at all possible.
  • If you cannot do it in a single SQL Statement, then do it in PL/SQL.
  • If you cannot do it in PL/SQL, try a Java Stored Procedure.
  • If you cannot do it in Java, do it in a C external procedure.
  • If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

I find it's a very usefull rule of thumb.

Upvotes: 3

Related Questions