RE: Function based index with <>

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "stmontgo@xxxxxxxxx" <stmontgo@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Aug 2009 17:14:57 -0400


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:

Hope that helps,


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

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



Other related posts: