Re: How to get query to use an index
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Thu, 13 Apr 2006 09:41:12 +0300
Trying to remeber something from set theory ....
Let's imagine you get set A from the first satement in UNION and set B
from the second statement in UNION. If intersection of A and B is
empty set then it makes no difference either to use UNION or UNION ALL
(except that oracle anyway performs sort unique in case of UNION). But
if intersection of A and B is not empty set as in example where row
with both c1 and c2 = 'x', then it is important because ORed
expression gives back only one instance of this particular row but
UNION ALL gives us two.
So if you are sure that always will be only one true either c1 = 'x'
or c2='x' then you can use UNION ALL and it should perform better
because of lack of sort unique.
But if you aren't sure and there may be cases when both c1 = 'x' and
c2 = 'x' then you have to use just UNION.
Gints
2006/4/12, Tim Gorman <tim@xxxxxxxxx>:
> Michael,
>
> Very interesting! Expanding your test to include just using plain UNION
> operator (which performs a DISTINCT), the results become correct.
>
> So, I had always thought UNION-ALL was equivalent to an OR'd expression; is
> it really UNION?
>
> Thanks!!!
>
> -Tim
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
