Explain Plan Question

I have a query in which several tables are joined. The WHERE clause consists of the equijoins to the tables from the driving table & a limitation on the value of the primary key of the driving table. The kicker is that the limitation on the driving table consists of an IN, because I want to query on two values. The Explain Plan shows that the query will use all unique indexes as shown below:

SELECT STATEMENT Cost =
  2.1 SORT AGGREGATE
    3.1 CONCATENATION

      4.1 NESTED LOOPS
        5.1 NESTED LOOPS
          6.1 TABLE ACCESS BY ROWID TABLE_1
           7.1 INDEX UNIQUE SCAN TABLE_1_PK UNIQUE
          6.2 INDEX UNIQUE SCAN TABLE_2_NDX1 UNIQUE
        5.2 INDEX RANGE SCAN TABLE_3_NDX1 UNIQUE
      4.2 NESTED LOOPS
        5.1 NESTED LOOPS
          6.1 TABLE ACCESS BY ROWID TABLE_1
            7.1 INDEX UNIQUE SCAN TABLE_1_PK UNIQUE
          6.2 INDEX UNIQUE SCAN TABLE_2_NDX1 UNIQUE
        5.2 INDEX RANGE SCAN TABLE_3_NDX1 UNIQUE




-------------------------------------------------------------------------------------

However, if I change one of the joins to be an outer join, I get this explain plan:

 SELECT STATEMENT Cost =
  2.1 SORT AGGREGATE
    3.1 NESTED LOOPS

      4.1 NESTED LOOPS OUTER
        5.1 TABLE ACCESS FULL TABLE_1
        5.2 INDEX RANGE SCAN TABLE_2_NDX1 UNIQUE
      4.2 INDEX RANGE SCAN TABLE_3_NDX1 UNIQUE




-------------------------------------------------------------------------------------

TABLE_1 is a large table (approx 350,000 rows) so I am thinking that the

first explain plan would run faster than the second. I'm guessing that Oracle does not agree & does not want do an outer join more than once, even if the join uses a unique index. Am I right in this assumption? Any suggestions on how to get around this problem? I don't have any other field to add to the WHERE clause that can use some other index on the driving table.

Thanks,

Julie Received on Wed May 12 1999 - 18:54:14 CDT