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
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
There is a 3rd way which is faster than 'IN' or multiple 'WHERE' conditions:
select *
from table_1 as tb1
inner join table_2 as tb2
where tb1.column_name = tb2.column_name
Here table_2 (or query) would have required values that were listed in 'IN' and 'WHERE' conditions in your example.