3 table query question

From: <spam_at_spam.sucks.com>
Date: 23 Jun 98 15:47:47 GMT
Message-ID: <aiesec.898616867@hercules>

Hi everyone:

I have a question for the Oracle gurus in this forum.

Situation:
When I query from 2 tables, the explain plan table shows the index path is found and used, resulting in a good quick query. But when I add a third table, the index on the first table is no longer used, and the query takes much more time.

Environment: Oracle 7.3.3 (mainframe) SQL*Net, SQLCoder. Oracle is using cost based optimization.

Here is the detail.

/* query 1 */

select count(*)
from table1,

        table2
where (table1.field1 = table2.field1 );

 SELECT STATEMENT Cost = 102
   SORT AGGREGATE

     NESTED LOOPS
	INDEX FULL SCAN table1_alternate_index
        INDEX UNIQUE SCAN table2_primary_key

/* query 2 */

select count(*)
from table1,

        table2,
        table3
WHERE   (table1.field1 = table2.field1 )
and     (table1.field2 = table3.field2 );

 SELECT STATEMENT Cost = 408
   SORT AGGREGATE

     NESTED LOOPS
       NESTED LOOPS
         TABLE ACCESS FULL table1
         INDEX UNIQUE SCAN table2_primary_key
       INDEX UNIQUE SCAN table3_primary_key                                

table1.field2 is also indexed, and table3.field2 is the primary key for that table.

If I force query 2 to use the index it used in query 1, I get an even greater cost; = not as efficient.

select /* index (table1 table1_alternate_index) */ count(*)
from table1,

        table2,
        table3

etc.

SELECT STATEMENT     Cost = 2248
  SORT AGGREGATE                                                                
    NESTED LOOPS                                                                
      NESTED LOOPS                                                              
        TABLE ACCESS BY ROWID table1
          INDEX FULL SCAN table1_alternate_index
        INDEX UNIQUE SCAN table2_primary_key
      INDEX UNIQUE SCAN table3_primary_key

If I force the use of the index on table1.field2, it will use it, but it will yield me a cost of 696.

So it appears the best cost is when the optimizer determines the access paths itself; however it uses a full table scan on a relatively large table. (48k rows.)

So, can anyone tell me why it the optimizer would find it more optimal in this case to not use the index?

Note: the data is very normalized. I can not join table3.field2 to table2.

Thanks for any help on this.

Bonus question: since the optimizer has to parse the statement to determine the best access paths anyway, why would it take noticeably longer to parse a query when you do a "select field1 from ..." as opposed to a "select count(*) from ..."? (since the tables and join conditions are identical.)

Bill

in an effort to keep from getting spammed, please manually reply to: bill_at_NOSPAM.gpfn.sk.ca (remove NOSPAM.)

Many thanks! Received on Tue Jun 23 1998 - 10:47:47 CDT