Performance of query without using OR clause

Question

I am facing a problem. I have one query

Select * from tabA 
where (a) in (a,b,c) 
OR b in (a,b,c) 

I want to facing performance issue due to this query as I need to remove the or condition , so I tried with the following query:

Select * from tabA 
where (a,b) in (a,b,c) 

but this query seems not to work, please help. I dont want to use 'or' condition.

Answer 1

if the logic remains the same - you may try a UNION

Select * from tabA  
where (a) in (a,b,c)  
union
Select * from tabA  
where b in (a,b,c)  

also, check your indexes and explain plan results - indexing may solve the original OR issues.

Answer 2

You use wrong syntax, if you want pair compare values you should use smth like this:

select * from tabA 
where (a,b) in ((a,b), (a,c), (b,c) etc.

Anyway in condition is transformed to multiple or conditions during query execution.

Provided you show table structure and execution plan people will be able to help you more effectively.

Answer 3

If you logically need the OR condition, then that is what you need. There is nothing wrong with using OR. If both columns are indexed then the query is likely to take no longer than running these 2 queries independently:

select * from tabA 
where a in (a,b,c);

select * from tabA 
where b in (a,b,c);

The optimizer may well do that and concatenate the results like this:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=256)
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABA' (Cost=2 Card=1 Bytes=128)
   3    2       INDEX (RANGE SCAN) OF 'TABA_A_IDX' (NON-UNIQUE) (Cost=1 Card=1)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABA' (Cost=2 Card=1 Bytes=128)
   5    4       INDEX (UNIQUE SCAN) OF 'TABA_B_IDX' (NON-UNIQUE) (Cost=1 Card=1)