Function Index

From: SaraN <Vishmayaa_at_yahoo.nospam.com>
Date: Tue, 20 Feb 2001 13:42:18 -0500
Message-ID: <3A92BA8A.4D83A207@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 Tue Feb 20 2001 - 12:42:18 CST