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 -> Re: Function-based indexes -- please help!

Re: Function-based indexes -- please help!

From: John Russell <netnews5_at_johnrussell.mailshell.com>
Date: Thu, 01 May 2003 04:12:35 GMT
Message-ID: <9871bv0h5ioul0ppsf2tr21llkp076062e@4ax.com>


On 30 Apr 2003 11:34:33 -0700, alex_at_sinoma.com (Alex Vilner) wrote:
>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?

2 possibilities, don't know for sure though:

The function needs to be declared DETERMINISTIC. Maybe try declaring your own function that just returns this same value, and use DETERMINISTIC in its declaration. If NVL() is completely out of the running (i.e. if you still get an error because it's called inside your function), maybe replace it with some IF-THEN logic.

The function-based index likes to know the longest possible value that might be returned by a VARCHAR2 function. Maybe create the index on SUBSTR(function(params),1,longest_possible_returned_string).

John

--
Photo gallery: http://www.pbase.com/john_russell/
Received on Wed Apr 30 2003 - 23:12:35 CDT

Original text of this message

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