Re: UNION: SQL-tuning

Hallo Niall,

I could understand you arguments for your example, but in my case q1 and q1 select from different tables. Table t1 with full scan is only in q1 but not in q2. That is why I thought, that optimizer-strategy for table t1 from q1 is like one for q1 union q2.

Thanks & Cheers

Andrei

>
> 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.
>
Received on Thu Sep 12 2002 - 04:45:54 CDT