Re: Function-based indexes -- please help!

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Wed, 30 Apr 2003 21:08:09 +0100
Message-ID: <3eb02d97_2@mk-nntp-1.news.uk.worldonline.com>

"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

I may wll be missing something, but I can't see much wrong with this, except that the 9999 year is out of range, IIRC. Have you tried the same thing with the year 4000?

HTH,
Paul Received on Wed Apr 30 2003 - 15:08:09 CDT