RE: Function based index with <>

From: Bobak, Mark <>
Date: Wed, 19 Aug 2009 17:14:57 -0400
Message-ID: <>


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: [] On Behalf Of steve montgomerie Sent: Wednesday, August 19, 2009 4:41 PM To:
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



-- Received on Wed Aug 19 2009 - 16:14:57 CDT

Original text of this message