Re: Optimizing Sql - unable to use index
Date: Sat, 17 Jul 2004 02:08:19 GMT
Message-ID: <nS%Jc.97938$IQ4.32623_at_attbi_s02>
"Haider Kazmi" <hkazmi_at_despammed.com> wrote in message
news:f1a5e8ad.0407160632.53abd233_at_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???
Are the datatypes of the joined fields the same? If not that might be the problem. Also is the table and indexes analyzed? Are you retrieving a small percentage of the rows or something larger like 10% or more? (that can cause a full table scan because it might be faster.)
What is the explain plan? What is the tkprof output? Jim Received on Sat Jul 17 2004 - 04:08:19 CEST