CODEWITHSUNDEEP
sqloracle-databaseplsql
Niger
Niger

Reputation: 4006

Oracle Performance IN or OR

Which operator in oracle gives better performance IN or OR

ex:

select * from table where y in (1,2,3)

or

select * from table where y = 1 or y = 2 or y = 3

Upvotes: 3

Views: 700

Answers (4)

David
David

Reputation: 1315

I would hesitate to use OR like that. You need to be careful if you add additional criteria. For instance adding an AND will require you remember to add parenthesis.

eg:

select * from table where y = 1 or y = 2 or y = 3

gets changed to:

select * from table where ( y = 1 or y = 2 or y = 3 ) AND x = 'value'

It is quite easy to forget to include the parenthesis and inject a difficult to daignose bug. For maintainability alone I would strongly suggest using IN instead of OR.

Upvotes: 4

DVK
DVK

Reputation: 129403

In a simple query like yours, the optimizer is smart enough to treat them both 100% the same so they are identical.

HOWEVER, that is potentially not 100% the case.

E.g. when optimizing large complex joints, it is plausible that the optimizer will not equate the two approaches as intelligently, thus choosing the wrong plan. I have observed somewhat similar problem on Sybase, although I don't know if it exists in Oracle (thus my "potentially" qualifyer).

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

The two statements are equivalent, the optimizer will generate the same access path. Therefore you should choose the one that is the most readable (I'd say the first one).

Upvotes: 8

Eric Petroelje
Eric Petroelje

Reputation: 60498

You'd want to do an explain plan to be sure, but I would expect the performance to be identical.

Upvotes: 9

Related Questions