Re: UNION: SQL-tuning

"Andrei Romazanov" <romazanov.andrei_at_gfos.de> wrote in message news:alpddu$1rt0js$1_at_ID-70985.news.dfncis.de...
> Hello all,
>
> I have performance-problem in a UNION.
> Normally I can separated optimize the parts from UNION,
> in this case however show both part-SELECTS good performance,
> but in UNION suddenly appears FULL TABLE SCAN. Could someone explain to
 me
> this
> behavior of the Optimizer?

Assuming stats are up to date then it probably goes someting like this. I have two queries q1 and q2 that select the same result columns from the same table for different subsets of data. they both return say 10% of the rows in the table. This means that an index access path is quite likely to be chosen since you are selecting a relatively small part of the data. Now I decide to UNION them and so am selecting 20% of the table. As I'm now selecting quite a large amount of the table Oracle decides that it will be quicker to to a scan through the table (reading db_multiblock_read_count worth of data at a time) than to revisit many table blocks many times as implied by an index access path.

You can test tis out by putting index hints in the subqueries and comparing the performance.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************