Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Explain Plan Question

Re: Explain Plan Question

From: Dante <dnotari_at_my-dejanews.com>
Date: Thu, 13 May 1999 11:12:31 GMT
Message-ID: <7hec2t$1c6$1@nnrp1.deja.com>


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
>
> ----------------------------------------------------------------------



>
> 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
>
>

--== 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US