Re: Oracle Index / Optimizer Problem

Perhaps because one of the fields (T1.B) is nullable, it seems like the rule-based optimizer considers I1 and I2 the same and uses the index that was last touched (in cache). I talked to the vendor and turns out that someone must have run into this problem before(of course), they already modified the order to the index in a newer version, thus I1 and I2 are no longer considered equal.

The solution is to change I2 from (T1.A, T1.D, T1.B) to (T1.D, T1.A, T1.B). The query came back with the result instantaneously. Problem solved.

I want to thank you all for your input.

• Francis

In article <6e94ca$lkp$1_at_nnrp1.dejanews.com>,   francis.ko_at_latimes.com wrote:
>
> 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
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 18 1998 - 00:00:00 CST