Search code examples
sqlperformanceoracledatabase-performancequery-performance

In clause versus OR clause performance wise

I have a query as below:

select * 
from table_1 
where column_name in ('value1','value2','value3');

considering that the data in such a table may be in millions, will the below restructuring help better??

select * 
from table_1 where 
column_name = 'value1' 
or column_name = 'value2' 
or column_name ='value3';

or

select * 
from table_1 
where column_name = any ('value1','value2','value3');

I need to know performance benefits also if possible.

Thanks in advance

Solution

  • the query doesn't matter much in case of 3 value checking only.

    Oracle will re-write the query anyways to match the best option available.

    in case there were more values and that too dynamic then the in clause or inner join could have been better.

    its best to leave the query as it is currently