Re: function index creation syntax

From: roger <xrsr_at_rogerware.com>
Date: Tue, 30 Sep 2003 19:28:17 GMT
Message-ID: <Xns94067FBDE5C1Drsrrogerwarecom@204.127.204.17>

theo_reh_at_yahoo.com (Ted) wrote in
news:1d9d3087.0309301040.ff473f9_at_posting.google.com:

> roger <xrsr_at_rogerware.com> wrote in message
> news:<Xns94057AFE77AAArsrrogerwarecom_at_204.127.199.17>... 

>> theo_reh_at_yahoo.com (Ted) wrote in
>> news:1d9d3087.0309251522.3ec01f39_at_posting.google.com:
>>
>> > Use a substr() in your index DDL.
>> >
>> > Ted-
>>
>>
>> This works, insofar as the syntax of creating the FBI goes:
>>
>> create index foo on some_table ( substr(some_func(some_col), 1,
>> 255) );
>>
>> Now, my question is whether by using the substr here, I have to
>> also use it when I query the table using some_func, in order for the
>> index to be recognized and used?
>>
>> That is, can I do this:
>>
>> select * from some_table
>> where some_func(some_col) = 'some value'
>>
>> or would I need to do this:
>>
>> select * from some_table
>> where substr(some_func(some_col), 1, 255) = 'some value'
>>
>>
>> Thanks.

> 
> 
> Test it.
> ... omitted ...

Sure, I can test it. And I very much appreciate your taking the time to do so and show the results:)

But, it sort of seems like one should be able to predict what the behavior will be based on experience and/or the documentation, without having to do that sort of thing.

Call me naive. Just my (non oracle) software background showing through again I guess.

My experience told me that I would probably have to match the test for the column exactly to the function signature as given in the index (which was why I posted).
Yours probably did too, and this is what your results indicate.

My experience tells me further that this is going to be a weak point in the construction of a system, and that I might be better to avoid using the FBI in my particular case, if this is the way it is. I think I may be asking/expecting/wishing for oracle to do more than it is capable of doing well, and that's not a place I want to be.

>
> HTH Yes, very much.
Thanks.

roger Received on Tue Sep 30 2003 - 14:28:17 CDT