re: ordering tables changes cost/plan in 9.2.0.6

Are there function based indexes included?

I ran into a bug appr. a year ago where even the resultset of a query could change(!) if:

A) A function based index on (UPPER(cola)) was available
B) The FBI was created before the index on (cola) was created
C) The where clause contained predicates on UPPER(cola)

This bug was reproducable from 8i till 10gR1.

It was fixed, and we got a backport to the 9i version at use by then.

If you like I can send you the script to reproduce the bug. I've no details at hand where I am now. I can give more details later today if anyone is interested.

Regards, Carel-Jan

===
If you think education is expensive, try ignorance. (Derek Bok) ===

> hello all,
> I was under the impression that one would not have to worry about the
> table order in an OPTIMIZER_MODE=CHOOSE type query. I was recently
> presented with a query whereby the cost did not change that much, but
> the plan did change drastically by just changing the tables' order in
> the "FROM" clause.
>
> What can I infer from this? Is this "phenomenon" pretty widespread?
> thx,
> Cos
>

--
http://www.freelists.org/webpage/oracle-l