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.
0 comments:
Post a Comment