Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Explain Plan Question
Julie,
sure ... the first one would be faster
but ...
both queries will return different results.
As you are using an outer join, you specify that you want to have ALL rows. That is why you get the full table scan.
When the optimizer knows that it will need all rows, why should it use the index ?
But as I said ... your queries will return different results.
Regards
Dante
In article <373A1499.83E48EEC_at_bellsouth.net>,
Julie A Peronto <jperonto_at_bellsouth.net> 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
>
> ----------------------------------------------------------------------
--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 13 1999 - 06:12:31 CDT