Re: Oracle Index / Optimizer Problem

Personally, I'd say screw the vendor and try the Cost -based mode. Analyze everything, including the indexes. Then try it and see what the plan looks like. Peoplesoft used to say that on Rule-based could be used, and we proved them wrong, too.

Alan
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