Re: Oracle ignoring function-based index

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 22 Jul 2005 06:27:20 -0700
Message-ID: <1122038840.481580.223120@g14g2000cwa.googlegroups.com>

Its the lack of stats that is causing your problem (so long as you have both the privs given above (query_rewrite ... ):-

http://asktom.oracle.com/~tkyte/article1/

Function based indexes are only used by the cost based optimiser, not the rule one. When optimizer_mode is set to choose, rule mode is used unless AT LEAST one table in the query has stats. If the query is a join, and one of the tables have stats, the cost based optimiser will still be used (default stats will be given to the other tables). If none of the tables have stats, rule based optimisation will be used. Received on Fri Jul 22 2005 - 08:27:20 CDT

Re: Oracle ignoring function-based index - why?