Re: Function-based indexes -- please help!

John Russell <netnews5_at_johnrussell.mailshell.com> wrote:

>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

I also suspect that a function-based index can ONLY be defined as a non-compound index-- Create 2 indexes, 1 for col1,col2,col3 and one for the date function - just be sure to reference them both in your where clauses, etc...

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Thu May 01 2003 - 08:58:44 CDT