Re: Function based index not being used

<bsandell_at_gmail.com> wrote in message
news:1120696686.956598.55860_at_f14g2000cwb.googlegroups.com...
>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'
>

Other people have already mentioned the need to enable cost based optimization, and for 8i the parameter settings

    query_rewrite_enabled = true
    query_rewrite_integrity = trusted.

After that, the main problem with your example is that 'aaa' is not a raw value, so your predicate is suffering from an implicit conversion which disables the index, viz:

    RAWTOHEX(otherUser.myFunction(object_name, ...)) = 'aaa';

for the purposes of testing, you probably need:

    otherUser.myFunction(object_name, ...) = HEXTORAW('aaa');

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005