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 < 10; 9 rows created. SQL> 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'