Re: Correlated subquery produces better plan than single table query?

A copy of this was sent to "a" <a_at_x.y>
(if that email address didn't require changing) On Fri, 6 Aug 1999 18:39:59 +1000, you wrote:

>Does this make sense?
>

no, you have it backwards

The first plan says:

goto the index, find rows such that cola = 1234 and then goto those rows in the table and check if colb is null.

The second plans says:

loop over EVERY single row in TabA.

   goto the index, find rows such that cola = 1234 and then goto those rows in

   the table and check if colb is null. end loop

the second plan does the first plan only it does it once for every row in the table instead of doing it once.

costs cannot be compared in any way shape or form across plans. only costs of different plans for the SAME query can be compared and even then only by the optimizer. the optimizer develops >1 plan for a query and picks the one with the lowest cost given a number of factors. things like hints, optimizer goals and such will affect these numbers.

>Table TabA has ColA as PK column, and ColB is not indexed.
>
>Statement is:
>
>select 'Y'
>into VarA
>from TabA
>where ColA = 1234 -- some existing value
>and ColB is null;
>
>Stats for this gives:
>SELECT cost=2
> TABLE ACCESS (BY INDEX ROWID) cost=2
> INDEX (UNIQUE SCAN) cost=1
>
>7 recursive calls
>1 rows processed
>
>If I change the statement to:
>
>select 'Y'
>into VarA
>from dual
>where exists
> (select 1
> from TabA
> where ColA = 1234
> and ColB is null);
>
>I get:
>
>SELECT cost=1
> FILTER
> TABLE ACCESS (FULL) OF DUAL cost=1
> TABLE ACCESS (BY INDEX ROWID) OF TABA cost=2
> INDEX (UNIQUE SCAN) cost=1
>
>0 recursive calls
>1 rows processed
>
>Why would the second one be better, when the access path to the table is
>unchanged? Also, why is the ultimate cost of the second one only 1, when
>along the way it incurred a cost of 2?
>
>Thanx for any knowledge impartation.
>
>-ak
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 06 1999 - 09:05:26 CDT