UNION All VS OR in oracle

[CrystalUser1]

Hi,

I am using oracle 9i. I have written a database view using UNION ALL to get the required data.

I am getting the same data using OR condition also. Which is good in performance?

I tested both, OR condition view is taking more time than UNION ALL View. Can anybody explain which is good and why?

thanks

 

[Turkbear]

Hi,
Can you run an explain plan of each version?
If possible, post the 2 versions of the query.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalUser1]

When i try to run the explain plan in toad, it says "specified plan table not found".

the example query with OR

select * from a,b

where (a.x=b.x and a.y='abc'etc., OR a.x=b.x and A.Y='XYZ'etc.,)

group by ...

the example query with UNION ALL

select * from a,b

where (a.x=b.x and A.y='abc'....)

UNION ALL

select * from a,c

where (a.x=b.x and a.y='XYZ' ...)

 

[CrystalUser1]

when i try to run the explain plan in toad, i am getting this error" specified plan table not found".

example query using OR:

select * from a,b where

(a.x=b.x and a.y='ABC' OR a.x=b.x and a.y='XYZ')

example query using UNION ALL:

select * from a,b where
(a.x=b.x and a.y='ABC")

union ALL

SELECT * FROM a,b where (a.x=b.x and a.y='XYZ')

 

[sem]

You should create plan table or get access to already created, if any. Actually Oracle optimizer may build similar execution plans for both options. Or different. So in general the answer is "it depends"

Regards, Dima

http://seminihin.narod.ru

 

[juliane26]

Generally an OR should be preferred, since using UNION ALL is always a little challenge for the optimizer to see that it actually needs to scan an index/table only once and not twice.

I'd also go and update statistics. See who will then be faster.

Also it appears you are not sending the query you are actually running. With additional conditions coming using ORDER or GROUP operations things can be different.

Dima is very right, only a plan will show what the optimizer is doing ....

Juliane

 

[sem]

I don't agree that OR should be preferred. Sometimes it's a great pain to make optimizer evaluate OR to UNION, in some cases it just refuses to use index, assuming that 2 values is too much. But I agree that in most cases it may save logical reads from other tables.

Regards, Dima

http://seminihin.narod.ru

 

[CrystalUser1]

Hi,

I tested both queries and UNION All taking less time. So I am using UNION All. Thanks for all your help.

thanks