First match

(Written-15-10-2007)

I was recently asked when presented with a query along the lines:

SQL> select * from
  2  ( select x
  3    from   t1
  4    where  x = :b1
  5    union all
  6    select x
  7    from   t2
  8    where  x = :b1
  9  )
 10  where rownum = 1
 11  /

then will the “ROWNUM” lead Oracle to be smart enough to stop after the “top” half
of the UNION ALL if it gets row back ? First we create tablespaces for tables to be stored, and plonk a table
in each.

SQL> create tablespace TS1 datafile '/data/oradata/IMS1DE/t1.dbf' size 10m;

Tablespace created.

SQL> create tablespace TS2 datafile '/data/oradata/IMS1DE/t2.dbf' size 10m;

Tablespace created.

SQL> create table T1 ( x number) tablespace ts1;

Table created.

SQL> insert into T1
  2  select rownum from dual connect by level  create table T2 ( x number) tablespace ts2;

Table created.

SQL> insert into T2
  2  select rownum from dual connect by level < 10;

9 rows created.

Now the big question is, if I am doing a “find the first row and exit”
style of query, will Oracle be smart enough to stop before it hits table T2 ?

SQL> alter tablespace t2 offline;

Tablespace altered.

SQL> select * from t2
               *
ERROR at line 1:
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/data/oradata/IMS1DE/t2.dbf'

SQL> variable b1 number
SQL> exec :b1 := 1

PL/SQL procedure successfully completed.

SQL> select * from
  2  ( select x
  3    from   t1
  4    where  x = :b1
  5    union all
  6    select x
  7    from   t2
  8    where  x = :b1
  9  )
 10  where rownum = 1
 11  /

         X
----------
         1

and just to reinforce it, I’ll reset “b1” to zero, so it will not find
anything in T1 and hence will need to move on to T2

SQL> variable b1 number
SQL> exec :b1 := 0

PL/SQL procedure successfully completed.

SQL> select * from
  2  ( select x
  3    from   t1
  4    where  x = :b1
  5    union all
  6    select x
  7    from   t2
  8    where  x = :b1
  9  )
 10  where rownum = 1
 11  /
 from   t2
         *
ERROR at line 7:
ORA-00376: file 70 cannot be read at this time
ORA-01110: data file 70: '/data/oradata/IMS1DE/t2.dbf'