user defined fct indexes vs 'normal' fct indexes

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 - 01:00:38 CST