Re: Function based index not being used

Matthias Hoys wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1120710314.897520.39550_at_g14g2000cwa.googlegroups.com...
> >
> >
> > 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
> >

>
>

>
>

Thank you for providing that information; after examining my own instance and the instances I monitor I, too, found the same parameter and permission set.

I guess we all miss something from time to time.

David Fitzjarrell Received on Thu Jul 07 2005 - 16:15:45 CDT