Use a hint to force the index to be used. If it is used with the hint,
then you have an issue with why the optimizer is not choosing to use it.
If its still not used with the hint, then most likely you have setup
problem that is not allowing it to be used. There are a couple of
parameters that have to be set - query_rewrite_enabled has to be true,
query_rewrite_integrity has to be at least 'trusted' and you have to
statistics on the table and index so you might want to analyze again to
make sure. There are also things with the optimizer mode and
compatibility parameter but if you're at 9.2 I'd guess those are set
okay. Using the alias in the where clause shouldn't be a problem, but
you could also do a simpler query without the join to see if that is the
problem.

HTH.


-----Original Message-----
From: Johnson, Shaunn [mailto:[Email Address Removed]
Sent: Tuesday, April 20, 2004 5:57 PM
To: LazyDBA.com Discussion
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