CODEWITHSUNDEEP
performanceoracle-databaseplsql
M.J.
M.J.

Reputation: 16646

Performance of query without using OR clause

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.

Upvotes: 1

Views: 2933

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132570

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)

Upvotes: 5

andr
andr

Reputation: 1646

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.

Upvotes: 2

Randy
Randy

Reputation: 16677

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.

Upvotes: 3

Related Questions