Re: How to get query to use an index

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%';

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

Other related posts: