Re: user defined fct indexes vs 'normal' fct indexes

Hi Rene,

At least you need to set these in your session in order to use FBI's :

alter session set query_rewrite_enabled = true; alter session set query_rewrite_integrity = trusted;

FBI's work with CBO only. Be sure it is enabled and be sure to have updated statistics on table qq_tab.

• Kenneth Koenraadt

rene.nyffenegger_at_gmx.ch (Rene Nyffenegger) wrote in message news:<a6d06107.0301192300.34ea20eb_at_posting.google.com>...
> Hello
>
> I am trying to make Oracle 9.2.0.1.0 on solaris to use my user
> defined function. And I don't have any success. Here's what happens:
>
>
>
> create or replace function qq_fnct (v in varchar2)
> return varchar2 deterministic
> as
> begin
> return translate(upper(v),':AEIOU',':');
> end;
> /
>
> create table qq_tab(
> a number,
> b varchar2(30)
> );
>
> create index qq_ix on qq_tab(qq_fnct(b));
>
>
> explain plan for select * from qq_tab where qq_fnct(b) = 'ABC';
>
> SELECT STATEMENT ()
> TABLE ACCESS (FULL) QQ_TAB
>
>
> Oracle chooses not to use my index. So, I use an explicit hint:
>
> explain plan for select /*+ index (qq_tab qq_ix) */ * from qq_tab
> where qq_fnct(b) = 'ABC';
>
>
> However, to my dissapointment, Oracle still doesn't want to use my
> index:
>
> SELECT STATEMENT ()
> TABLE ACCESS (FULL) QQ_TAB
>
>
> I then tried to use upper:
>
> drop index qq_ix;
>
> create index qq_ix on qq_tab(upper(b));
>
> explain plan for select * from qq_tab where upper(b) = 'XXX';
>
> SELECT STATEMENT ()
> TABLE ACCESS (FULL) QQ_TAB
>
>
> This was/is sort of expected... well why should Oracle not use my
> function
> but use another when it comes to indexing. But then, I used a hint
> again:
>
>
>
> explain plan for select /*+ index (qq_tab qq_ix) */ * from qq_tab
> where upper(b) = 'ABC';
>
> SELECT STATEMENT ()
> TABLE ACCESS (BY INDEX ROWID) QQ_TAB
> INDEX (RANGE SCAN) QQ_IX
>
>
> All of a sudden, Oracle chooses the function based index. I am
> confused.
> Is there an explanation?
>
> Any help as always appreciated.
>
> Rene
Received on Mon Jan 20 2003 - 07:52:21 CST