Re: Function based index with <>
Date: Thu, 20 Aug 2009 22:56:27 -0400
Thanks everyone for the fantastic suggestions. I've got some great ideas to try out. I'll let you know if I find a good fix. The app is Oracle Fusion analytics with HR and Procurment Modules. So this all sits in Oracle Business Intelligence EE. I lovely litle point and click app that generates queries like magic for super users and they do so ad hoc so. Although I do see some consistencies through the queries they can easily change.
On Wed, Aug 19, 2009 at 4:40 PM, steve montgomerie <stmontgo_at_gmail.com>wrote:
> 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