From: kennedy-downwithspammersfamily@attbi.net   
      
   "Haider Kazmi"  wrote in message   
   news: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???   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)