Re: Function Index

hi Sara,
i think that rtrim function don't use index. Try the select statement again, but don't use rtrim, ltrim etc.. in your where condition

Carlo

"SaraN" <Vishmayaa_at_yahoo.nospam.com> ha scritto nel messaggio 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 Thu Feb 22 2001 - 11:24:49 CST