Re: Function based index not being used

bsandell_at_gmail.com wrote:
> 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?
> Also, the function is not owned by the user creating the index, but the
> function is executable by public.
>
> Any ideas why the index is not used? Thanks.
>
> -- Recreate the function as deterministic so it can be used in a
> function based index.
>
> CREATE OR REPLACE FUNCTION myFunction(inData VARCHAR2, ...)
> RETURN RAW
> DETERMINISTIC
> AS
> LANGUAGE java
> NAME 'com....myFunction (java.lang.String ...) return byte[]';
> /
>
>
> -- Create table with sample data
> create table indxtest as select * from all_objects where rownum <=
> 1000;
>
> -- Create function-based index
> create index indxtest_func_idx2 on
> indxtest(otherUser.myFunction(object_name, ...));
>
> -- try to select using the function based index
> select * from indxtest where otherUser.myFunction(object_name, ...) =
> 'aaa';
>
> no rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'INDXTEST'
Function-based indexes require the CBO, which requires statistics on your table. In the absence of statistics the CBO will make a 'guess' as to what those statistics might be; usually this 'guess' is not the best. You also have but 1000 rows in your table, and I'll take a guess that optimizer_index_cost_adj has not been touched and remains at its default of 100. At that value the cost of an index scan is roughly the same as the cost of a full table scan.

You need to compute statistics on your table, and adjust the optimizer_index_cost_adj initialization parameter to a value less than 100; I use a value of 15. Once this parameter is set you'll need to restart the database for it to take effect.

I expect once you have current statistics you'll see this index being used. You'll need to keep them current by scheduling one of the dbms_stats procedures through dbms_job. This has been discussed many times in this newsgroup so you should search for any of the numerous threads on this topic.

I hope this helps.

David Fitzjarrell Received on Wed Jul 06 2005 - 23:25:14 CDT