OraFAQ Forum: SQL & PL/SQL » Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE’

Home » SQL & PL/SQL » SQL & PL/SQL » Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE’ (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance difference between using ‘IN’ or ‘OR’ in the ‘WHERE’ [message #353842]

Wed, 15 October 2008 05:24

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

zaff
Messages: 50
Registered: July 2008

Member

Thanks Michel - That makes sense.

Report message to a moderator

Previous Topic:	OPEN Cursor at Package Level
Next Topic:	Problem while emailing attachment attachment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:03:20 CST 2024