You're query shouldn't use an index. You are joining
TRIM(A.CONTRACT_ID_NUM)=TRIM(B.CONTRACT_ID_NUM)'
You're not looking for a subset of crontract_id_num. but everyone of each
table. therefore full tablescan has the least cost.

Mike
[Email Address Removed] Original Message -----
From: "Johnson, Shaunn" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Tuesday, April 20, 2004 5:56 PM
Subject: query not using functional index


> Howdy:
>
> Running Oracle 9i rel 2.
>
> I have a table that is about 15 million records.
> I have a functional index (TRIM("CONTRACT_ID_NUM"))
> on the column CONTRACT_ID_NUM.
>
> I try to join on different tables using the same
> column name and after looking at the table (via
> the console manager), it seems that the entire table
> is being scanned row by row and the index isn't being
> used.
>
> Question: Does it matter that when I join, I match
> by saying 'where TRIM(A.CONTRACT_ID_NUM)=TRIM(B.CONTRACT_ID_NUM)'
> *and* have the index designed as TRIM(CONTRACT_ID_NUM)?
> I can't think of a reason why TRIM would have this effect,
> but I don't know for sure.
>
> Suggestions?
>
> Thanks!
>
> -X
>
Oracle LazyDBA home page