The first matching row

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s