Re: Function based index not being used

<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
>

You also need the QUERY REWRITE privilege and QUERY_REWRITE_ENABLED=TRUE in init.ora ...

HTH
Matthias Hoys Received on Thu Jul 07 2005 - 12:43:38 CDT