In article <3d009254$0$4732$afc38c87_at_news.easynet.co.uk>, "Mark" says...
>
>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
>
>
Lets see the plan, the create indexes, the create table.
--
Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Fri Jun 07 2002 - 07:27:03 CDT
