RE: Function based index with <>

Steve,

First, yes, you can create an expression based index.  In fact, that's why the 
DBA_IND_EXPRESSIONS view exists.  They're actually all expressions.

Been a while since I've played with this.....

Try something like this:

create index my_exp_indx on w_employee_daily_snp_f(case when delete_flg <> 'Y' 
then delete_flg end);

note that the index created above will ONLY have rows where delete_flg <> 'Y', 
since NULLs are not stored in a B* Tree index.

and then do:
select whatever from w_employee_daily_snp_f where (case when delete_flg <>'Y' 
then delete_flg end) = delete_flg;

Tom Kyte has written quite a bit on AskTom.  Check this thread, and there are  
others as well:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:667694821129

Hope that helps,

-Mark

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of steve montgomerie
Sent: Wednesday, August 19, 2009 4:41 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Function based index with <>

Oracle 10.2.0,4 on OEL 4.5.

I have an app generating SQL which I can't change. The following sql causes FTS 
on a big table
 AND DELETE_FLG <> 'Y'

I ran the sql tuning advisor and it actually made a suggestion.

"Rewrite the predicate into an equivalent form to take advantage of indices. 
Alternatively, create a function-based index on the expression."

Can I create an FBI on an expression? I didn't think that was possible.

No nulls in the mentioned column.

I've been trying various combination's and can't create the index

SQL> create index blah on W_EMPLOYEE_DAILY_SNP_F(DELETE_FLG,<>);
create index blah on W_EMPLOYEE_DAILY_SNP_F(DELETE_FLG,<>)
                                                       *
ERROR at line 1:
ORA-00936: missing expression

thanks

steve

Other related posts: