Oracle Performance IN or OR

Question

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

Answer 1

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

Answer 2

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).

Answer 3

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).

Answer 4

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.