(Non-)Use of indexes

Hi,

I'm attempting to tune an SQL statement, but I cannot get the optimiser to use one of the indexes upon one of the tables in the query.

The SQL looks like this:

SELECT ...
FROM table1 t1
, table2 t2
, table3 t3

WHERE t3.column = t2.column1
AND t2.column2 = t1.column

The optimiser uses indexes upon tables t3 and t2 as required. However, it doesn't use the one on t1 when linking to t2. The columns are VARCHAR(t2) and NUMBER(t1) respectively. I've tried using an explicit TO_NUMBER on the t2 column, and an INDEX hint too, but neither work in getting the optimiser to use the index.

If I remove t3 and just link t1 and t2, the index is used by the optimiser.

Any suggestions why this should be would be appreciated, as I'm baffled.

Ta

Mark Received on Fri Jun 07 2002 - 06:02:49 CDT