| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimization of UNION
Michael Keppler wrote:
>
> Hello everybody !
>
> If I have to queries on the same columns of the same tables, will the
> optimizer make a OR statement from a UNION? (I think, OR will be faster
> normally, right?)
>
> For example, If I use
>
> select col1
> from table1
> where col2=xyz
> union
> select col1
> from table2
> where col3=abc
>
> will this be optimized to "where col2=xyz or col3=abc" ?
> And if yes, can the optimizer do this also on queries which do not have
> all (but some) the same tables in the select statement ? Could I give
> the optimizer a hint for this ?
>
> Ciao, Michael.
>
> --
> Michael Keppler, MCSE
> IT logic GbR
> Michael.Keppler_at_gmx.de
You can do an EXPLAIN which will tell you...Oracle will occasionally switch between the two...
for example, you may issue "select ... union select ..." and oracle just does the one scan of the table (ie as if it were an OR), and vice versa...
Interesting, I was giving a tuning course the other day, and this intersting thing came up
In 7.3
select ...
from table
where un_indexed_col = val1
UNION ALL
select ...
from table
where un_indexed_col = val1
was (correctly) changed to OR and thus one scan by the optimiser...The same thing in 8.0.5 (same schema, blocksize, tables etc) did not...Hmmm
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 09 1999 - 07:01:31 CDT
 |
 |