Re: Explain Plan Question

Your outer join will return different results. But sometimes you can try HINTING to use a certain index if you wish.

Larry Overbeck

Julie A Peronto wrote:

> 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 Thu May 13 1999 - 11:09:22 CDT