Posted by 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 /
and asked – will the “ROWNUM” lead Oracle to be smart enough to stop after the “top” half of the UNION ALL if it gets row back ?
Let’s find out. First we create tablespaces for tables to be stored, and plonk a table in each.
SQL> create tablespace TS1 datafile 'C:\ORACLE\ORADATA\NP12\T1.DBF' size 10m;
Tablespace created.
SQL> create tablespace TS2 datafile 'C:\ORACLE\ORADATA\NP12\T2.DBF' size 10m;
Tablespace created.
SQL> create table T1 ( x number) tablespace ts1;
Table created.
SQL> insert /*+ APPEND */ 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 /*+ APPEND */ 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 ts2 offline;
Tablespace altered.
SQL> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\NP12\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;
X
----------
1
Yes it does! 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;
from t2
*
ERROR at line 7:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\NP12\T2.DBF'
SQL>
Nice to know also that even if we need to do dynamic sampling, we’re still not going to get in trouble. I’ll repeat the tests without the APPEND hint, so the tables do not pick up auto statistics.
SQL> create tablespace TS1 datafile 'C:\ORACLE\ORADATA\NP12\T1.DBF' size 10m;
Tablespace created.
SQL> create tablespace TS2 datafile 'C:\ORACLE\ORADATA\NP12\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.
SQL> alter tablespace ts2 offline;
Tablespace altered.
SQL> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\NP12\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;
X
----------
1
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;
from t2
*
ERROR at line 7:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\NP12\T2.DBF'
SQL>
SQL> alter tablespace ts2 online;
Tablespace altered.
SQL> set autotrace on
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;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 957534384
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 13 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - filter("X"=TO_NUMBER(:B1))
5 - filter("X"=TO_NUMBER(:B1))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Learning is not a spectator sport 