RE: How to get query to use an index
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <gints.plivna@xxxxxxxxx>, <tim@xxxxxxxxx>
- Date: Thu, 13 Apr 2006 18:48:29 -0400
Excellent point, Gints, and I have registered you for the punster hall of
fame. (Though I suppose if the application was indeed normal the union would
not be a problem.) Since Oracle allows duplicate tuples, we indeed need to
watch out for Union as well as for union all, though in the OP's orginal
post he would need duplicates on all the columns in the tuple from one half
of the OR. Since the query could be for less than all the columns of the
table, though, the table might even have legitimate normal data while the
projection could contain duplicate tuples. In that case I really would
wonder what the desired result would be and whether it would be decided by
the consumer of the query result set or a religious war amongst the sects of
relational algebra. Writing the cross product in's and not in's would really
be tedious, but still might out perform the OR depending on the data
texture.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Gints Plivna
Sent: Thursday, April 13, 2006 8:17 AM
To: tim@xxxxxxxxx
Cc: oracle-l
Subject: Re: How to get query to use an index
Yea and BTW there are cases (though in normal applications rare) when
you have to use OR, because neither UNION nor UNION ALL works.
You can easily take the example provided by Michael and simply insert row
insert into mgsx values('x','x'); two times - so you got different
results for each of three variations. So you simply have to be careful
and know your data and know what you really need as output.
SQL> select * from mgsx where c1 like 'x%' or c2 like 'x%';
C1 C2
---------- ----------
x y
y x
x x
x x
4 rows selected.
SQL> select * from mgsx where c1 like 'x%'
2 union all
3 select * from mgsx where c2 like 'x%'
4 /
C1 C2
---------- ----------
x y
x x
x x
y x
x x
x x
6 rows selected.
SQL> select * from mgsx where c1 like 'x%'
2 union
3 select * from mgsx where c2 like 'x%'
4 /
C1 C2
---------- ----------
x x
x y
y x
3 rows selected.
Gints
2006/4/13, Gints Plivna <gints.plivna@xxxxxxxxx>:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
