Re: Function-based indexes -- please help!

Niall Litchfield wrote:
>
> <disclaimer>
> I have never actually done this so it could be all crap
> </disclaimer>
>
> IIRC only deterministic functions are allowed for function based indexes. I
> guess NVL doesn't meet the requirements.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
> "Alex Vilner" <alex_at_sinoma.com> wrote in message
> news:22e9f6e0.0304301034.570a1229_at_posting.google.com...
> > I have followed the things suggested on this (and other sites) for
> > minimum requirements for the function-based index creation:
> >
> > QUERY REWRITE privilege granted to the index creator
> >
> > COMPATIBLE set to 8.1.7
> >
> >
> > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE
> >
> > ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED
> >
> > ... and I get the following errors:
> > CREATE UNIQUE INDEX index1
> > ON table1 (col1, col2, col3, NVL(dateCol1, TO_DATE('12/31/9999',
> > 'MM/DD/YYYY')))
> >
> > ORA-01031: insufficient privileges
> >
> > ... or, if the index creation line is modified to this:
> > CREATE UNIQUE INDEX index1
> > ON table1 (col1, col2, col3, NVL(dateCol1, '31-12-9999'))
> > , a different error:
> >
> > ORA-01743: only pure functions can be indexed
> >
> > Any ideas on how to work around this?
> >
> > Thank you in advance!
> >
> > --Alex

in which case, DECODE can be used as a substitute

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"