Re: Oracle Index / Optimizer Problem

This answer may be slightly misleading. A separate sort operation *can* be avoided if the ORDER BY sequence is identical to that of the index chosen for access.
However, it *is* correct to say that an index is chosen primarily on the basis of the predicates in the WHERE clause. Unfortunately, the original poster did not include this clause in his question. If it included predicates involving columns T1.A, T1.B and T1.C then I would expect the index I1 to be used. However, if it has a predicate on T1.A only, then either index can be used.
Which one is actually used by the rule-based optimiser depends on the order in which the index rows are retrieved from the dictionary, which in turn will be dependent on the order in which the indexes were created.

HTH. In article <6eabdh$608$1_at_news.ipass.net>, "David Sisk" <davesisk_at_ipass.net> wrote:

> Oracle's optimizer will not use an index instead of a sort for an ORDER BY.
> Indexes are used by the WHERE clause (with some exceptions). All sorts
> (such as ones required by an ORDER BY, GROUP BY, etc.) are done in memory
> (or written to disk in the TEMP tablespace if too large for memory).
>
>
> francis.ko_at_latimes.com wrote in message <6e94ca$lkp$1_at_nnrp1.dejanews.com>...
> >Please consider the following SQL :
> >
> >SELECT .....
> >FROM T1, T2, T3, T4, T5, T6, T7
> >WHERE .....
> >ORDER BY T1.A, T1.B, T1.C
> >
> >Two of T1's nonunique indexes are : I1 (T1.A, T1.B, T1.C)
> > I2 (T1.A, T1.D, T1.B)
> >snip
Received on Wed Mar 18 1998 - 00:00:00 CST