Re: How to get query to use an index

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_at_evdbt.com> 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_at_CeDeT.dk 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%';
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l