<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
Received on Wed Apr 30 2003 - 15:03:02 CDT
