RE: Function based index with <>
Date: Wed, 19 Aug 2009 17:14:57 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F1D9E15C0_at_AAPQMAILBX02V.proque.st>
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:14:57 CDT