Correlated subquery produces better plan than single table query?

Does this make sense?

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 Received on Fri Aug 06 1999 - 03:39:59 CDT