Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Function-based indexes -- please help!

Function-based indexes -- please help!

From: Alex Vilner <alex_at_sinoma.com>
Date: 30 Apr 2003 11:34:33 -0700
Message-ID: <22e9f6e0.0304301034.570a1229@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 - 13:34:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US