Oracle Tips and Tricks — David Fitzjarrell

July 16, 2008

Preserving The Union

Filed under: General — dfitzjarrell @ 16:01

A question was recently posted in an Oracle-related newsgroup which asked if it was possible to preserve the order of individual result sets in a union. The answer is a resounding “Yes, and No”, as it depends upon how you want that union to behave and what you want to implement to arrive at that result. Taking a basic query of the EMP table and adjusting it to produce four different sets of distinct EMPNO values let’s see if we can ‘get there from here’. Our first query produces the following results:

SQL> with emp1 as (
  2        select empno, ename, job, sal
  3        from emp
  4        order by 4
  5  )
  6  select *
  7  from emp1;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMYTHE     CLERK            800
      7935 SMITH      CLERK            900
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   330 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        628  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

So far, so good as the results are ordered as we expect. Modifying that query a bit to generate another result set produces:

SQL> with emp2 as(
  2        select empno+100 empno, ename, job, sal
  3        from emp
  4        order by 4
  5  )
  6  select *
  7  from emp2;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7469 SMYTHE     CLERK            800
      8035 SMITH      CLERK            900
      8000 JAMES      CLERK            950
      7976 ADAMS      CLERK           1100
      7621 WARD       SALESMAN        1250
      7754 MARTIN     SALESMAN        1250
      8034 MILLER     CLERK           1300
      7944 TURNER     SALESMAN        1500
      7599 ALLEN      SALESMAN        1600
      7882 CLARK      MANAGER         2450
      7798 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7666 JONES      MANAGER         2975
      7888 SCOTT      ANALYST         3000
      8002 FORD       ANALYST         3000
      7939 KING       PRESIDENT       5000

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   330 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        628  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

And we do that again:

SQL> with emp3 as(
  2        select empno+200 empno, ename, job, sal
  3        from emp
  4        order by 4
  5  )
  6  select *
  7  from emp3;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7569 SMYTHE     CLERK            800
      8135 SMITH      CLERK            900
      8100 JAMES      CLERK            950
      8076 ADAMS      CLERK           1100
      7721 WARD       SALESMAN        1250
      7854 MARTIN     SALESMAN        1250
      8134 MILLER     CLERK           1300
      8044 TURNER     SALESMAN        1500
      7699 ALLEN      SALESMAN        1600
      7982 CLARK      MANAGER         2450
      7898 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7766 JONES      MANAGER         2975
      7988 SCOTT      ANALYST         3000
      8102 FORD       ANALYST         3000
      8039 KING       PRESIDENT       5000

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   330 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        628  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

And one more time:

SQL> with emp4 as(
  2        select empno+300 empno, ename, job, sal
  3        from emp
  4        order by 4
  5  )
  6  select *
  7  from emp4;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7669 SMYTHE     CLERK            800
      8235 SMITH      CLERK            900
      8200 JAMES      CLERK            950
      8176 ADAMS      CLERK           1100
      7821 WARD       SALESMAN        1250
      7954 MARTIN     SALESMAN        1250
      8234 MILLER     CLERK           1300
      8144 TURNER     SALESMAN        1500
      7799 ALLEN      SALESMAN        1600
      8082 CLARK      MANAGER         2450
      7998 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7866 JONES      MANAGER         2975
      8088 SCOTT      ANALYST         3000
      8202 FORD       ANALYST         3000
      8139 KING       PRESIDENT       5000

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4060621227

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    15 |   330 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        628  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

We, indeed, have four differing result sets to combine, which will produce a total of 60 records. Writing the expected query, using UNION, produces disastrous results with respect to our initial data ordering:

SQL> with emp1 as (
  2        select empno, ename, job, sal
  3        from emp
  4        order by 4
  5  ),
  6  emp2 as(
  7        select empno+100 empno, ename, job, sal
  8        from emp
  9        order by 4
 10  ),
 11  emp3 as(
 12        select empno+200 empno, ename, job, sal
 13        from emp
 14        order by 4
 15  ),
 16  emp4 as(
 17        select empno+300 empno, ename, job, sal
 18        from emp
 19        order by 4
 20  )
 21  select *
 22  from emp1
 23  union
 24  select *
 25  from emp2
 26  union
 27  select *
 28  from emp3
 29  union
 30  select *
 31  from emp4;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMYTHE     CLERK            800
      7469 SMYTHE     CLERK            800
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7566 JONES      MANAGER         2975
      7569 SMYTHE     CLERK            800
      7599 ALLEN      SALESMAN        1600
      7621 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7666 JONES      MANAGER         2975
      7669 SMYTHE     CLERK            800

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7698 BLAKE      MANAGER         2850
      7699 ALLEN      SALESMAN        1600
      7721 WARD       SALESMAN        1250
      7754 MARTIN     SALESMAN        1250
      7766 JONES      MANAGER         2975
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7798 BLAKE      MANAGER         2850
      7799 ALLEN      SALESMAN        1600
      7821 WARD       SALESMAN        1250
      7839 KING       PRESIDENT       5000

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7844 TURNER     SALESMAN        1500
      7854 MARTIN     SALESMAN        1250
      7866 JONES      MANAGER         2975
      7876 ADAMS      CLERK           1100
      7882 CLARK      MANAGER         2450
      7888 SCOTT      ANALYST         3000
      7898 BLAKE      MANAGER         2850
      7900 JAMES      CLERK            950
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7935 SMITH      CLERK            900

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7939 KING       PRESIDENT       5000
      7944 TURNER     SALESMAN        1500
      7954 MARTIN     SALESMAN        1250
      7976 ADAMS      CLERK           1100
      7982 CLARK      MANAGER         2450
      7988 SCOTT      ANALYST         3000
      7998 BLAKE      MANAGER         2850
      8000 JAMES      CLERK            950
      8002 FORD       ANALYST         3000
      8034 MILLER     CLERK           1300
      8035 SMITH      CLERK            900

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8039 KING       PRESIDENT       5000
      8044 TURNER     SALESMAN        1500
      8076 ADAMS      CLERK           1100
      8082 CLARK      MANAGER         2450
      8088 SCOTT      ANALYST         3000
      8100 JAMES      CLERK            950
      8102 FORD       ANALYST         3000
      8134 MILLER     CLERK           1300
      8135 SMITH      CLERK            900
      8139 KING       PRESIDENT       5000
      8144 TURNER     SALESMAN        1500

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8176 ADAMS      CLERK           1100
      8200 JAMES      CLERK            950
      8202 FORD       ANALYST         3000
      8234 MILLER     CLERK           1300
      8235 SMITH      CLERK            900

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4269941287

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    60 |  2340 |    20  (85)| 00:00:01 |
|   1 |  SORT UNIQUE          |      |    60 |  2340 |    20  (85)| 00:00:01 |
|   2 |   UNION-ALL           |      |       |       |            |          |
|   3 |    VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|   7 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   9 |    VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|  10 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  11 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|  12 |    VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|  13 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  14 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1888  bytes sent via SQL*Net to client
        267  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL>

The key to all of this is the highlighted text in the query plan, the SORT UNIQUE. Oracle, as directed, creates a UNION of all four result sets then proceeds to order them to ‘cull out’ any duplicates. Even though there are none Oracle still performs the sort to ensure no duplicates exist; this is simply how UNION operates. We can ‘outwit’ Oracle by including a ‘bogus’ column to sort on, but then we run into the possibility of processing an extra column of values unrelated to the actual data we desire:

SQL> with emp1 as (
  2        select 1, empno, ename, job, sal
  3        from emp
  4        order by 5
  5  ),
  6  emp2 as(
  7        select 2, empno+100 empno, ename, job, sal
  8        from emp
  9        order by 5
 10  ),
 11  emp3 as(
 12        select 3, empno+200 empno, ename, job, sal
 13        from emp
 14        order by 5
 15  ),
 16  emp4 as(
 17        select 4, empno+300 empno, ename, job, sal
 18        from emp
 19        order by 5
 20  )
 21  select *
 22  from emp1
 23  union
 24  select *
 25  from emp2
 26  union
 27  select *
 28  from emp3
 29  union
 30  select *
 31  from emp4;

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         1       7369 SMYTHE     CLERK            800
         1       7499 ALLEN      SALESMAN        1600
         1       7521 WARD       SALESMAN        1250
         1       7566 JONES      MANAGER         2975
         1       7654 MARTIN     SALESMAN        1250
         1       7698 BLAKE      MANAGER         2850
         1       7782 CLARK      MANAGER         2450
         1       7788 SCOTT      ANALYST         3000
         1       7839 KING       PRESIDENT       5000
         1       7844 TURNER     SALESMAN        1500
         1       7876 ADAMS      CLERK           1100

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         1       7900 JAMES      CLERK            950
         1       7902 FORD       ANALYST         3000
         1       7934 MILLER     CLERK           1300
         1       7935 SMITH      CLERK            900
         2       7469 SMYTHE     CLERK            800
         2       7599 ALLEN      SALESMAN        1600
         2       7621 WARD       SALESMAN        1250
         2       7666 JONES      MANAGER         2975
         2       7754 MARTIN     SALESMAN        1250
         2       7798 BLAKE      MANAGER         2850
         2       7882 CLARK      MANAGER         2450

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         2       7888 SCOTT      ANALYST         3000
         2       7939 KING       PRESIDENT       5000
         2       7944 TURNER     SALESMAN        1500
         2       7976 ADAMS      CLERK           1100
         2       8000 JAMES      CLERK            950
         2       8002 FORD       ANALYST         3000
         2       8034 MILLER     CLERK           1300
         2       8035 SMITH      CLERK            900
         3       7569 SMYTHE     CLERK            800
         3       7699 ALLEN      SALESMAN        1600
         3       7721 WARD       SALESMAN        1250

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         3       7766 JONES      MANAGER         2975
         3       7854 MARTIN     SALESMAN        1250
         3       7898 BLAKE      MANAGER         2850
         3       7982 CLARK      MANAGER         2450
         3       7988 SCOTT      ANALYST         3000
         3       8039 KING       PRESIDENT       5000
         3       8044 TURNER     SALESMAN        1500
         3       8076 ADAMS      CLERK           1100
         3       8100 JAMES      CLERK            950
         3       8102 FORD       ANALYST         3000
         3       8134 MILLER     CLERK           1300

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         3       8135 SMITH      CLERK            900
         4       7669 SMYTHE     CLERK            800
         4       7799 ALLEN      SALESMAN        1600
         4       7821 WARD       SALESMAN        1250
         4       7866 JONES      MANAGER         2975
         4       7954 MARTIN     SALESMAN        1250
         4       7998 BLAKE      MANAGER         2850
         4       8082 CLARK      MANAGER         2450
         4       8088 SCOTT      ANALYST         3000
         4       8139 KING       PRESIDENT       5000
         4       8144 TURNER     SALESMAN        1500

         1      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
         4       8176 ADAMS      CLERK           1100
         4       8200 JAMES      CLERK            950
         4       8202 FORD       ANALYST         3000
         4       8234 MILLER     CLERK           1300
         4       8235 SMITH      CLERK            900

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4269941287

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    60 |  2520 |    20  (85)| 00:00:01 |
|   1 |  SORT UNIQUE          |      |    60 |  2520 |    20  (85)| 00:00:01 |
|   2 |   UNION-ALL           |      |       |       |            |          |
|   3 |    VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   6 |    VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|   7 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   8 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   9 |    VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|  10 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  11 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|  12 |    VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|  13 |     SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  14 |      TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1925  bytes sent via SQL*Net to client
        267  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL>

It does, of course, produce the desired ordering, but the result set is, as noted above, altered with essentially useless values. So, what is a person to do? Looking again at the query plans a common element of every UNION is the UNION-ALL operation. Every time Oracle produces a UNIONed result set it executes the UNION-ALL operation (an unsorted merge of the various result sets). It’s only during a UNION that the SORT UNIQUE is invoked. So, why not simply rid yourself of that pesky sort by simply performing a UNION ALL:

SQL> with emp1 as (
  2        select empno, ename, job, sal
  3        from emp
  4        order by 4
  5  ),
  6  emp2 as(
  7        select empno+100 empno, ename, job, sal
  8        from emp
  9        order by 4
 10  ),
 11  emp3 as(
 12        select empno+200 empno, ename, job, sal
 13        from emp
 14        order by 4
 15  ),
 16  emp4 as(
 17        select empno+300 empno, ename, job, sal
 18        from emp
 19        order by 4
 20  )
 21  select *
 22  from emp1
 23  union all
 24  select *
 25  from emp2
 26  union all
 27  select *
 28  from emp3
 29  union all
 30  select *
 31  from emp4;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMYTHE     CLERK            800
      7935 SMITH      CLERK            900
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000
      7469 SMYTHE     CLERK            800
      8035 SMITH      CLERK            900
      8000 JAMES      CLERK            950
      7976 ADAMS      CLERK           1100
      7621 WARD       SALESMAN        1250
      7754 MARTIN     SALESMAN        1250
      8034 MILLER     CLERK           1300

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7944 TURNER     SALESMAN        1500
      7599 ALLEN      SALESMAN        1600
      7882 CLARK      MANAGER         2450
      7798 BLAKE      MANAGER         2850
      7666 JONES      MANAGER         2975
      7888 SCOTT      ANALYST         3000
      8002 FORD       ANALYST         3000
      7939 KING       PRESIDENT       5000
      7569 SMYTHE     CLERK            800
      8135 SMITH      CLERK            900
      8100 JAMES      CLERK            950

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8076 ADAMS      CLERK           1100
      7721 WARD       SALESMAN        1250
      7854 MARTIN     SALESMAN        1250
      8134 MILLER     CLERK           1300
      8044 TURNER     SALESMAN        1500
      7699 ALLEN      SALESMAN        1600
      7982 CLARK      MANAGER         2450
      7898 BLAKE      MANAGER         2850
      7766 JONES      MANAGER         2975
      7988 SCOTT      ANALYST         3000
      8102 FORD       ANALYST         3000

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8039 KING       PRESIDENT       5000
      7669 SMYTHE     CLERK            800
      8235 SMITH      CLERK            900
      8200 JAMES      CLERK            950
      8176 ADAMS      CLERK           1100
      7821 WARD       SALESMAN        1250
      7954 MARTIN     SALESMAN        1250
      8234 MILLER     CLERK           1300
      8144 TURNER     SALESMAN        1500
      7799 ALLEN      SALESMAN        1600
      8082 CLARK      MANAGER         2450

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7998 BLAKE      MANAGER         2850
      7866 JONES      MANAGER         2975
      8088 SCOTT      ANALYST         3000
      8202 FORD       ANALYST         3000
      8139 KING       PRESIDENT       5000

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2237021712

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    60 |  2340 |    16  (82)| 00:00:01 |
|   1 |  UNION-ALL           |      |       |       |            |          |
|   2 |   VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|   3 |    SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   5 |   VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|   6 |    SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   8 |   VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|   9 |    SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  10 |     TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|  11 |   VIEW               |      |    15 |   585 |     4  (25)| 00:00:01 |
|  12 |    SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  13 |     TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1754  bytes sent via SQL*Net to client
        267  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL>

My golly gee whilikers, it works! Notice that each individual result set is ordered, but that the overall union of those results retains the desired order; the results are simply stacked one atop the other and the final result is displayed sans any additional sorting. Of course this can also preserve duplicated records which might not be a good thing, so using UNION ALL in place of UNION might not be the solution you need. Choosing the proper method for creating such UNIONed results is a decision only you can make, based upon the data with which you’re working.

We can take a previous example and complicate it further to make it work:

SQL> select empno, ename, job, sal
  2  from
  3  (with emp1 as (
  4          select 1, empno, ename, job, sal
  5          from emp
  6          order by 5
  7  ),
  8  emp2 as(
  9          select 2, empno+100 empno, ename, job, sal
 10          from emp
 11          order by 5
 12  ),
 13  emp3 as(
 14          select 3, empno+200 empno, ename, job, sal
 15          from emp
 16          order by 5
 17  ),
 18  emp4 as(
 19          select 4, empno+300 empno, ename, job, sal
 20          from emp
 21          order by 5
 22  )
 23  select *
 24  from emp1
 25  union
 26  select *
 27  from emp2
 28  union
 29  select *
 30  from emp3
 31  union
 32  select *
 33  from emp4);

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMYTHE     CLERK            800
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7566 JONES      MANAGER         2975
      7654 MARTIN     SALESMAN        1250
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7844 TURNER     SALESMAN        1500
      7876 ADAMS      CLERK           1100

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7900 JAMES      CLERK            950
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7935 SMITH      CLERK            900
      7469 SMYTHE     CLERK            800
      7599 ALLEN      SALESMAN        1600
      7621 WARD       SALESMAN        1250
      7666 JONES      MANAGER         2975
      7754 MARTIN     SALESMAN        1250
      7798 BLAKE      MANAGER         2850
      7882 CLARK      MANAGER         2450

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7888 SCOTT      ANALYST         3000
      7939 KING       PRESIDENT       5000
      7944 TURNER     SALESMAN        1500
      7976 ADAMS      CLERK           1100
      8000 JAMES      CLERK            950
      8002 FORD       ANALYST         3000
      8034 MILLER     CLERK           1300
      8035 SMITH      CLERK            900
      7569 SMYTHE     CLERK            800
      7699 ALLEN      SALESMAN        1600
      7721 WARD       SALESMAN        1250

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7766 JONES      MANAGER         2975
      7854 MARTIN     SALESMAN        1250
      7898 BLAKE      MANAGER         2850
      7982 CLARK      MANAGER         2450
      7988 SCOTT      ANALYST         3000
      8039 KING       PRESIDENT       5000
      8044 TURNER     SALESMAN        1500
      8076 ADAMS      CLERK           1100
      8100 JAMES      CLERK            950
      8102 FORD       ANALYST         3000
      8134 MILLER     CLERK           1300

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8135 SMITH      CLERK            900
      7669 SMYTHE     CLERK            800
      7799 ALLEN      SALESMAN        1600
      7821 WARD       SALESMAN        1250
      7866 JONES      MANAGER         2975
      7954 MARTIN     SALESMAN        1250
      7998 BLAKE      MANAGER         2850
      8082 CLARK      MANAGER         2450
      8088 SCOTT      ANALYST         3000
      8139 KING       PRESIDENT       5000
      8144 TURNER     SALESMAN        1500

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      8176 ADAMS      CLERK           1100
      8200 JAMES      CLERK            950
      8202 FORD       ANALYST         3000
      8234 MILLER     CLERK           1300
      8235 SMITH      CLERK            900

60 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 572280370

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    60 |  2340 |    20  (40)| 00:00:01 |
|   1 |  VIEW                  |      |    60 |  2340 |    20  (40)| 00:00:01 |
|   2 |   SORT UNIQUE          |      |    60 |  2520 |    20  (85)| 00:00:01 |
|   3 |    UNION-ALL           |      |       |       |            |          |
|   4 |     VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|   5 |      SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   6 |       TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|   7 |     VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|   8 |      SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|   9 |       TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|  10 |     VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|  11 |      SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  12 |       TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
|  13 |     VIEW               |      |    15 |   630 |     4  (25)| 00:00:01 |
|  14 |      SORT ORDER BY     |      |    15 |   330 |     4  (25)| 00:00:01 |
|  15 |       TABLE ACCESS FULL| EMP  |    15 |   330 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       1818  bytes sent via SQL*Net to client
        267  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL>

but we’ve now wrapped a large union with another query simply to remove the ordering column, a column we artificially generated to start with simply to get UNION to behave as we thought it should. But this may be the only option should there be duplicate data, since UNION ALL returns everything and that may not generate the required result set. Notice also that we need to use distinct in the outer query since, by adding the ‘sort by’ column we also introduce the possibility of duplicates in the data.

We see it is possible to preserve the order of individual result sets in a UNION operation, it simply depends upon which UNION avenue you choose to pursue. And, as mentioned before, that choice is ultimately up to you.

But, it is nice to have choices.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: