Re: SQL Humor

Paul wrote:
> Mike Hodgson wrote:
> > The EXISTS() predicate is typically a fairly efficient predicate because
> > it only needs to scan until it gets a match, at which time it returns.
> > The worst case scenario (it finds a match on the last physical row, or
> > it doesn't find any matching row) is the same I/O as the IN() predicate
> > case because IN() will evaluate the entire subquery.
>
> Why does IN() need to evaluate the entire subquery? Couldn't it in
> theory work exactly the same as EXISTS() at the physical level?

Yes it could, and indeed does (Oracle 9i). In the following example, IN and EXISTS are processed the same way, and DO NOT evaluate the entire subquery:

SQL> create table t1 as select object_id, object_name from all_objects;

Table created.

SQL> alter table t1 add constraint t1_pk primary key (object_id);

Table altered.

SQL> create table t2 as select object_id, object_name from all_objects where rownum=1;

Table created.

SQL> alter table t2 add constraint t2_pk primary key (object_id);

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> select count(*) from t1;

  COUNT(*)

     47355

SQL> select count(*) from t2;

  COUNT(*)

         1

SQL> set autotrace on
SQL> select * from t2 where object_id in (select object_id from t1);

 OBJECT_ID OBJECT_NAME

---------- ------------------------------
     18164 /1005bd30_LnkdConstant

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=30)    1 0 NESTED LOOPS (Cost=1 Card=1 Bytes=30)

   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=1 Bytes=26)
   3    1     INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
          5  consistent gets
          3  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from t2 where exists (select null from t1 where t1.object_id = t2.object_id);

 OBJECT_ID OBJECT_NAME

---------- ------------------------------
     18164 /1005bd30_LnkdConstant

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)    1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=1 Bytes=26)
   3    1     INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE) (Cost=1 Card=1 B
          ytes=4)

Statistics

          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed