Re: Function based index with <>

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Wed, 19 Aug 2009 16:50:25 -0500
Message-Id: <7835B5AF-99AE-432D-BF2A-05F2919E2CF7_at_enkitec.com>

How about a simple where delete_flg = 'N' or where delete_flg in ('N','S',,'T',...).

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Aug 19, 2009, at 4:14 PM, Bobak, Mark wrote:

> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
> ] On Behalf Of steve montgomerie
> Sent: Wednesday, August 19, 2009 4:41 PM
> To: oracle-l_at_freelists.org
> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 19 2009 - 16:50:25 CDT