Re: How to get query to use an index
- From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
- To: tim@xxxxxxxxx, 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Apr 2006 19:08:09 -0700 (PDT)
I don't think that it is logically equivalent to either, or at least it depends
based on
circumstances. It's equivalent to a "UNION ALL" if there is a single column
"OR" (eg. col1 = 'A'
or col1 = 'B') because thesame row can't match both conditions, but if it is a
multiple column
"OR" (col1 = 'A' or col2 = 'A') then the same result might be returned by a
UNION, a UNION ALL, or
neither depending on whether the result set is distinct, and on whether there
are rows that match
both conditions.
DA
http://oraclesponge.blogspot.com
--- Tim Gorman <tim@xxxxxxxxx> wrote:
> 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
>
>
> on 4/12/06 12:25 AM, Michael Garfield Sørensen, CeDeT at mgs@xxxxxxxx wrote:
>
> > SQL> create table mgsx(c1 varchar2(10),c2 varchar2(10));
> >
> > Tabel er oprettet.
> >
> > SQL> insert into mgsx values('x','y');
> >
> > 1 række er oprettet.
> >
> > SQL> insert into mgsx values('x','x');
> >
> > 1 række er oprettet.
> >
> > SQL> insert into mgsx values('y','x');
> >
> > 1 række er oprettet.
> >
> > SQL> select * from mgsx where c1 like 'x%' or c2 like 'x%';
> >
> > C1 C2
> > ---------- ----------
> > x y
> > x x
> > y x
> >
> > SQL> select * from mgsx where c1 like 'x%'
> > 2 union all
> > 3 select * from mgsx where c2 like 'x%';
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
