Re: Function based index with <>
Date: Thu, 20 Aug 2009 22:56:27 -0400
Message-ID: <5d4504220908201956r5cabe15ej638784e0cf49d998_at_mail.gmail.com>
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
>
> thanks
>
> steve
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 20 2009 - 21:56:27 CDT