CODEWITHSUNDEEP
oracle-database
Ankush Kaundal
Ankush Kaundal

Reputation: 11

Regarding Oracle query functional behaviour

CREATE TABLE ak_temp
(
  misc_1 varchar2(4000),
  misc_2 varchar2(4000),
  time_of_insert timestamp  
);

Query 1(Original) : 

select * 
  from ak_temp 
 where misc_1 in('ankush') 
    or misc_2 in('ankush')

Query 2 : 

select * 
  from ak_temp 
 where 'ankush' in (misc_1,misc_2)

Hi, I have somewhat similar problem with the queries, i want to avoid Query 1 since the cost in our live environment is coming bit on higher side so i have figured out Query 2 with less cost, are these both functionally equivalent?

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The two are functionally equivalent and should produce the same query plan, so one should not have a performance advantage over the other (or any such advantage would be miniscule). Oracle might be smart enough to take advantage of two indexes, one on ak_temp(misc_1) and one on ak_temp(misc_2).

However, you might also consider:

select t.* 
from ak_temp t
where misc_1 = 'ankush'
union
select t.*
from ak_temp t
where misc_2 = 'ankush';

This version will definitely take advantage of indexes, but the union can slow things down if many rows match the two conditions.

EDIT:

To avoid the union, you can do:

select t.* 
from ak_temp t
where misc_1 = 'ankush'
union all
select t.*
from ak_temp t
where misc_2 = 'ankush' and (misc_1 <> 'ankush' and misc_1 is not null);  -- or use `lnnvl()`

Upvotes: 3

Related Questions