Oracle Index / Optimizer Problem

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