Re: Function Index

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
Received on Wed Feb 21 2001 - 09:14:22 CST