Home » SQL & PL/SQL » SQL & PL/SQL » Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE’ (10g)
Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE’ [message #353842] Wed, 15 October 2008 05:24 Go to next message
zaff
Messages: 50
Registered: July 2008
Member
Hi guys,
I would like to know if it is better to use ‘IN’:

Select *
From atable
Where num IN (1423432,234234,243234,12112,………….);

OR

Select *
From atable
Where num=1423432 OR num=234234 OR num=243234 OR num=12112………….);

How does Oracle treat the two statements?

The list of numbers could potentially be close to or over a 1000. I believe the ‘IN’ statement has a limit of 1000.

Thanks in advance,
Zaff
Re: Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE [message #353845 is a reply to message #353842] Wed, 15 October 2008 05:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
In the first statement you need to type less and you have a limitation as you have already mentioned.

In the second statement you need to type more and the code will look lot bigger than it needs to be.

Instead you could populate those values in a temporary table and use it in the join condition as appropriate.

Also from next time if you could format your post, it will be much appreciated.

Regards

Raj
Re: Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE [message #353847 is a reply to message #353842] Wed, 15 October 2008 05:44 Go to previous messageGo to next message
zaff
Messages: 50
Registered: July 2008
Member
Thanks Raj for the prompt response and for stating the obvious Razz I don’t have an issue with the readability of the query or how much needs to be typed. I just need to know if Oracle treats the queries differently when the queries are executed. Is there a significant performance difference if I had a large list of numbers?

Thanks,
Zaff
Re: Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE&am [message #353852 is a reply to message #353847] Wed, 15 October 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No difference.
SQL> select * from emp where deptno in (10,20);

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   696 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     8 |   696 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10 OR "DEPTNO"=20)

As you can see Oracle internally translates IN into OR.

Regards
Michel

[Updated on: Wed, 15 October 2008 06:06]

Report message to a moderator

icon14.gif  Re: Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE [message #353865 is a reply to message #353842] Wed, 15 October 2008 06:32 Go to previous message
zaff
Messages: 50
Registered: July 2008
Member
Thanks Michel - That makes sense.
Previous Topic: OPEN Cursor at Package Level
Next Topic: Problem while emailing attachment attachment
Goto Forum:
  


Current Time: Tue Dec 06 02:45:55 CST 2016

Total time taken to generate the page: 0.09830 seconds