Optimizing Sql - unable to use index

From: Haider Kazmi <hkazmi_at_despammed.com>
Date: 16 Jul 2004 07:32:37 -0700
Message-ID: <f1a5e8ad.0407160632.53abd233@posting.google.com>

I need help trying to optimize a SQL query. I am using Oracle 8i.

I have a table with about 1.2 million records, lets call it T1. I am doing a join from another table, lets say T2 which has a restriction on it. T2's id is a foreign key on T1.

T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)

What happens is if I retrive just the T1.id from the query, it uses the T2id2T1id index for a reverse walk. However as soon as I retrieve some other column from T1, say T1.some_col, oracle decides to do a full table scan of T1.

Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does a full scan of this index.

Any clues to why oracle decides to do a full table scan on a 1.2million record table??? Received on Fri Jul 16 2004 - 09:32:37 CDT