Re: Function based index not being used

On Wed, 06 Jul 2005 17:44:40 -0700, bsandell interested us by writing:

> I am trying to create a function based index to speed up a query, but
> the index doesn't seem to be used. The user defined function is based
> on a java stored procedure, I'm not sure if that could be the problem?

You don't mention the Oracle version. From the 'Oracle9i Application Developer's Guide - Fundamentals' manual, Chapter 5 section on 'Restrictions for Function-Based Indexes'

--------"
Only cost-based optimization can use function-based indexes. Remember to set the QUERY_REWRITE_ENABLED initialization parameter to TRUE, and call DBMS_STATS.GATHER_TABLE_STATISTICS or DBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective. "-------

I don't recall seeing the gathering of stats in your example.

Assuming you have OTN access, direct link to the doc is http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg06idx.htm#7198

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***