Re: Oracle Index / Optimizer Problem

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)
>
>When I used EXPLAIN PLAN, I found out that the (rule-based) optimizer is
 not
>using index I1 (which is identical to the order by clause) instead, it was
>using I2. When I "rebuild" I1, same result. However, when I dropped and
>recreated I1, the optimizer started using I1. When I shutdown and restarted
>the DB, the optimizer started using I2 AGAIN. This whole sequence is
>reproducable.
>
>I read from an Oracle book that a SQL can have up to 5 indexes. Does that
>mean if a SQL has already used 5 indexes to join (my case), it will not use
>the 6th to sort ?
>
>Why would the rule-based optimizer decided not to use the very obvious
 index ?
>and why would it change its mind after I dropped and recreated the index ?
>
>This SQL is generated by a third-party software, I cannot alter the query
>(i.e. cannot limit the table joins nor use hints), furthermore, the vendor
>recommends rule-based optimizer rather than cost-based, how can I resolve
 this
>problem ?
>
>Any help is much appreciated. Thanks in advance.
>Please respond to francis.ko_at_latimes.com
>
>- Francis
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Thu Mar 12 1998 - 00:00:00 CST