Re: Function Index

On Wed, 21 Feb 2001 10:14:22 -0500, SaraN <Vishmayaa_at_yahoo.nospam.com> wrote:

>David,
>
>Thanks for the info. I tried the same and I does not work either. Have a
>question though. We are using RULE based optimiser. Would it be the
>cause of the problem? I am not sure. Pl. advise.
>
>Thanks
>
>SaraN
>vishmayaa_at_yahoo.com
>
>
>Hi,
>
>You need to have the function index the same as the where clause,
>including the order of functions.
>
>In your case, Oracle RDBMS considers they are not the same and
>result in full table scan.
>
>Try your SQL as follow :
>
>SELECT RTRIM(swValue)
> FROM temp123
> WHERE RTRIM(swName) = 'MY NAME'
> AND RTRIM(UPPER(swValue)) = 'MY COUNTRY'
>
>David
>
>"SaraN" <Vishmayaa_at_yahoo.nospam.com> wrote in message
>news:3A92BA8A.4D83A207_at_yahoo.nospam.com...
>> People
>>
>> I am trying to create a function index and use it in my query. But I
>> have a problem. To begin with, here is the index
>>
>>
>> create index sw_code_tst_idx1 on temp123 (
>> RTRIM(Name) ,
>> UPPER(RTRIM(Value))
>> )
>>
>> Now when I try to explain a query which should be using this index
>>
>> SELECT RTRIM(swValue)
>> FROM temp123
>> WHERE RTRIM(swName) = 'MY NAME'
>> AND UPPER(RTRIM(swValue)) = 'MY COUNTRY'
>>
>> I get a full table scan on this table. But if I use a hint on this
 query
>>
>> SELECT /*+ index(sw_code_tst_idx1) */RTRIM(swValue)
>> FROM temp123
>> WHERE RTRIM(swName) = 'MY NAME'
>> AND UPPER(RTRIM(swValue)) = 'MY COUNTRY'
>> then I get to use the index.
>>
>> Have anyone of you faced this thing before? If so, pl. explain how to
>> get to use this index without hinting.
>>
>> Thanks a lot IA
>>
>> SaraN
>> skethara_at_cisco.com
>> vishmayaa_at_yahoo.com

Rule based optimizer has been obsoleted (ie no new development) in the early days of v7. It doesn't know about parallel query, hash joins, anti joins, *function based indexes* and a whole lot of other new features.
You will have to switch to cost based optimizer, or at least to use cost for this table (ie analyze your table) to have the optimizer recognize the function based index.
Sticking with rule based optimizer should be avoided at all cost :) It will be removed in a future release of Oracle. You will find your self in a dead alley. In fact the results of CBO are many times better than RBO, once you know how to use it.
Advise: do *NOT* switch to CBO overnight. Almost any application developed using RBO will perform like a turtle with two legs in quicksand.

Hth,

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Feb 21 2001 - 12:00:39 CST