Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Function Index
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 Tue Feb 20 2001 - 18:23:33 CST