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_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