Oracle Tips and Tricks — David Fitzjarrell

July 30, 2008

Say "Cheese!"

Filed under: General — dfitzjarrell @ 13:56

Materialized views and their associated snapshot logs are interesting animals with respect to the exp utility. Attempting to export a snapshot log by itself only provides the snapshot log table definition, as does attempting to do so with a table-level export. But that’s not all you need with a materialized view/snapshot to enable a fast refresh scenario, you need the data IN that snapshot log as well. So, how do you go about successfully exporting that data?

There are two modes of a traditional, original exp which can, and will, export snapshot log data: full export mode and user-level export mode. As these two modes export every object in a schema (all schemas for a full-mode export) all dependent objects on a table will be exported, which includes snapshot logs and their data because to export the snapshot log data the master table must also be exported. Yes, a table-level export can copy the master table, but it doesn’t follow the dependency tree to copy other tables/objects (and, yes, indexes are exported with the table in a table-level export), thus the snapshot logs are, well, left behind. Think of it in this manner: with a table-level export any table can be exported, by itself, without having to export any dependent tables which may exist (tables upon which foreign key constraints are defined). As such snapshot logs are also not exported in table-level mode.

If you want/need snapshot logs exported, along with their data, then you must perform a full or user-level export. There is no other choice.

If the snapshot log is defined using ROWID it will be necessary to perform a complete refresh immediately after importing the schema, as the stored ROWID values will be useless. Once a complete refresh is run all subsequent fast refreshes should execute without error. This is not the case with a primary key-based snapshot log; even immediately after import fast refreshes are possible.

So, to avoid surprises later (meaning ‘mysteriously’ missing snapshot logs) when you have snapshots/materialized views perform nothing less than a user-level export. This ensures you get your snapshot log definitions and data. And keeps your materialized view/snapshot fast refresh jobs running smoothly.

Advertisements

July 25, 2008

"Event"-ually

Filed under: General — dfitzjarrell @ 19:07

I’ve heard this lament many, many times:

“I need to trace a session that is already connected but isn’t mine. How do I do that?”

It’s really a fairly easy task. Oracle provides three packaged procedures to make that possible:

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

DBMS_SYSTEM.SET_EV

DBMS_SUPPORT.START_TRACE_IN_SESSION

and, of the three, I prefer DBMS_SYSTEM.SET_EV as it provides a level of control not easily implemented with DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION or DBMS_SUPPORT.START_TRACE_IN_SESSION. The procedure is fairly easy to use (you must be connected as SYS to use it):

exec dbms_system.set_ev(<sid>,<serial#>, <event>, <level>, <name, usually NULL>)

So, if your user has been assigned sid 459 and serial# 11703 and you want a full-bore 10046 trace you would execute:

exec dbms_system.set_ev(459, 11703, 10046, 12, NULL)

and you’d have a level 12, 10046 event trace started on that session. Woohoo! So, what is this NM parameter used for? Oracle says it’s to document the trace name, and you can use it to ‘name’ the trace you’re starting so you can keep track of whose session you’re monitoring. Of course if you do provide a name string when you start the trace then you need to provide that same string when you terminate that trace else it won’t be stopped. So, I just leave it NULL.

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION is a rather simplistic interface to DBMS_SYSTEM.SET_EV, giving you the opportunity to start and stop a trace, period. No setting levels, no deciding what you want to see, just the ability to start and stop a basic trace on a session which is not your own. Oh, and there’s also the possibility that you won’t get a trace started with this interface (I don’t know why it fails, but I’ve had spotty results using this particular procedure.) I prefer to use the DBMS_SYSTEM.SET_EV procedure for that very reason.

DBMS_SUPPORT.START_TRACE_IN_SESSION provides a bit more flexibility by allowing you to include waits, binds, both or neither in your trace output:

exec dbms_support.start_trace_in_session(<sid>,<serial#>, <waits, boolean>, <binds, boolean>)

To use DBMS_SUPPORT.START_TRACE_IN_SESSION to effect the same level of trace you started using DBMS_SYSTEM.SET_EV you’d submit:

exec dbms_support.start_trace_in_session(459, 11703, TRUE, TRUE)

and you’d have the job done.

So you have the trace started, how on earth do you stop it? The trace will end when:

1) The traced session terminates
2) You execute DBMS_SYSTEM.SET_EV(sid,serial#, event, 0, NULL) if that’s the procedure you used to start the trace
3) You execute DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid,serial#) if you used DBMS_SUPPORT.START_TRACE_IN_SESSION

The DBMS_SYSTEM package is installed when you create a database; unfortunately the DBMS_SUPPORT package isn’t, so if you want to use it you’ll need to execute the dbmssupp.sql script in $ORACLE_HOME/rdbms/admin:

SQL> @?/rdbms/admin/dbmssupp

Don’t run this as any user other than SYS, or it’s not likely to work properly, if at all. And, since DBMS_SUPPORT isn’t installed by default that’s all the more reason to fire up DBMS_SYSTEM.SET_EV to trace other sessions.

Once the trace file is written you’d process it just like any other 10046 trace file, by using the tkprof utility to format the raw trace data into a really pretty report; the trace files generated by ‘alter session set events …’ and the DBMS_SYSTEM.SET_EV procedure are the same. The only difference is in how you get the event trace started.

Because Oracle support personnel needed the ability to trace user sessions the DBMS_SYSTEM package was created. Of course you shouldn’t be using this all of the time, on every session, because, in reality, not every session needs to be traced. And, if you do feel that need it’s simpler and easier to change the init.ora or spfile to enable event 10046 tracing.

You may not use this utility even once per year, but it’s nice to know you have access to it should the need arise. It’s always better to have something and not need it rather than need something and not have it. So tuck this information away for that ‘rainy day’ that will eventually arrive. I think you’ll be glad that you did.

July 17, 2008

Over Extended

Filed under: General — dfitzjarrell @ 19:31

Autoextend can be very helpful to busy DBAs by allowing Oracle to automatically increase file sizes to a pre-determined limit when necessary which can make the workday a bit easier by minimizing the effort expended to monitor disk space. In a heavily used system where inserts and updates eclipse the deletes autoextend gives the DBA some “breathing room” so he/she can attend to more pressing issues. For those unfamilar with using autoextend we’ll show how to enable it and how to manage the available space.

Setting a file in a tablespace to autoextend is a fairly easy task as long as the user performing the actions has DBA or SYSDBA privileges:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2000M;

Database altered.

SQL>

Not every datafile or tempfile in a tablespace needs to be set to autoextend–you can select one or more datafiles/tempfiles and leave others unaltered. The size limit, if left unset in the ‘alter database’ command, is one byte shy of 32G, which is the same value set by Oracle by setting MAXSIZE to UNLIMITED for a database using an 8k blocksize. Larger values can be set if the blocksize of the database is 16k or greater.

You have autoextend on for one or more datafiles/tempfiles; how do you monitor the size? The DBA_DATA_FILES/DBA_TEMP_FILES views provide almost all of the necessary information (what’s missing is the db_block_size, and that’s fairly easy to find):

SQL> select file_name, bytes, maxbytes,
 2 increment_by*(bytes/blocks) "INCREMENT",
 3 maxbytes-bytes remaining,
 4 (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
 5 from dba_data_files
 6 where autoextensible = 'YES'
 7 /

FILE_NAME                     BYTES  MAXBYTES INCREMENT REMAINING EXTENSIONS
-------------------------- -------- --------- --------- --------- ----------
/d909/data/users01.dbf     52428800 314572800  10485760 262144000         25

SQL>
SQL> select file_name, bytes, maxbytes,
2 increment_by*(bytes/blocks) "INCREMENT",
3 maxbytes-bytes remaining,
4 (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
5 from dba_data_files
6 where autoextensible = 'YES'
7 /

FILE_NAME                                                     BYTES   MAXBYTES INCREMENT  REMAINING EXTENSIONS
------------------------------------------------------ ------------ ---------- --------- ---------- ----------
+DATA/pspqa09/datafile/system.274.764412479               734003200 3.4360E+10  10485760 3.3626E+10       3206
+DATA/pspqa09/datafile/sysaux.275.764412499               629145600 3.4360E+10  10485760 3.3731E+10       3216
+DATA/pspqa09/datafile/undotbs1.276.764412513             214958080 3.4360E+10   5242880 3.4145E+10       6512
+DATA/pspqa09/datafile/psp_data01.281.764412629          3221225472 3.4360E+10      8192 3.1138E+10    3801086
+DATA/pspqa09/datafile/psp_idx01.283.764412761            536870912 3.4360E+10      8192 3.3823E+10    4128766
+DATA/pspqa09/datafile/users.284.764412775                  5242880 3.4360E+10   1310720 3.4354E+10      26210

6 rows selected.

SQL>

Notice the current size, the maximum size, the incremental value, space remaining and the extensions available are reported, to give you an idea of how much room is left to allocate to your autoextend datafile. You could execute this query on a daily basis to see how quickly or slowly your datafile is growing, then plan for either modifying the maxsize or adding another datafile. And all of this presumes you have sufficient disk space for the remaining extensions to occur; monitoring autoextend tablespaces also means monitoring the disk space at the operating system level or from your ASM instance to ensure that you don’t “overextend” yourself. The queries shown above can be ‘automated’ with cron to report, on a set schedule, how disk space is being used. The report can even be emailed to you:

#!/bin/ksh
#
#
# Variables
#
USER="blorpo"
PASS="bleebo"
DB="mydb"

SQLPLS=`which sqlplus`
MAILLIST="bob@ed.com"
FILE="report/datafile_rpt_`date "+%m%d%Y"`.txt"

if [ ! -d ./report ]
then
 mkdir report
fi

$SQLPLS /nolog < EOF
connect $USER/$PASS@$DB
@report_qry $FILE
EOF

if [ -s $FILE ]
then
 mailx -s "Datafile Space Report" $MAILLIST < $FILE
fi

Simply put the queries in a script named report_qry.sql and spool the output to a file (using the &1 positional parameter) and you can generate a dated report on a schedule, having it emailed right to your desk. The report_qry.sql script would look like this:

set pagesize 0 feedback off linesize 4000 trimspool on termout off set echo off verify off

spool &1

select file_name, bytes, maxbytes, 
increment_by*(bytes/blocks) "INCREMENT",
maxbytes-bytes remaining, 
(maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
from dba_data_files 
where autoextensible = 'YES' 
/

select file_name, bytes, maxbytes,
increment_by*(bytes/blocks) "INCREMENT",
maxbytes-bytes remaining,
(maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
from dba_data_files
where autoextensible = 'YES'
/

spool off

Changing the report is simply changing the query or queries in report_qry.sql.

Speaking of ASM how would you monitor the remaining disk space? It’s a straightforward query against the V$ASM_DISKGROUP view:

SQL> select name, total_mb, free_mb, cold_used_mb, hot_used_mb, usable_file_mb
 2 from v$asm_diskgroup
 3 where name is not null
 4 /

NAME                           TOTAL_MB FREE_MB COLD_USED_MB HOT_USED_MB USABLE_FILE_MB
------------------------------ -------- ------- ------------ ----------- --------------
DATA                             102400   87964        14436           0          87964

SQL>

The above query can also be ‘automated’ using the example above.

What’s the difference between HOT_USED_MB and COLD_USED_MB? It has to do with the disk region where the data lies. ASM allows the DBA to implement Intelligent Data Placement; disk regions can be specified as HOT and ASM will leverage the disk geometry to find the fastest sectors to access and put often accessed data in those sectors to improve performance. By default a diskgroup is considered COLD; adding a HOT template to the diskgroup prepares it for disk reassignment:

SQL> ALTER DISKGROUP data ADD TEMPLATE datafile_hot
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);

Diskgroup altered.

SQL>

This will work if the COMPATIBLE.RDBMS parameter is set to 11.2.0.0.0 or higher; if you happen to have both a 10g and 11g database using your 11.2 ASM instance executing the above command will display:

SQL> ALTER DISKGROUP data ADD TEMPLATE datafile_hot
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);
ALTER DISKGROUP data ADD TEMPLATE datafile_hot
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15283: ASM operation requires compatible.rdbms of 11.2.0.0.0 or higher

SQL>

For illustrative purposes we’ll presume we have the proper COMPATIBLE.RDBMS setting; the diskgroup is now prepared for this:

SQL> ALTER DISKGROUP data MODIFY FILE '+data/orcl/datafile/users.259.679156903'
 2 ATTRIBUTE ( 
 3 HOT
 4 MIRRORHOT);

Diskgroup altered.

SQL>

ASM will now manage your data (presuming you’re not using a storage array using striped volumes) and locate the ‘hot’ data in the HOT region. [Storage arrays with LUNs composed of stripes hide the disk geometry from ASM so it’s harder to determine where the HOT zone should be.]

Can you set a datafile or tempfile, already configured to autoextend, to have a maximum size? Certainly, and it’s done with the same command used to turn autoextend on with a maximum size. To change the setting for the file we set at the beginning of this post we would:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2200M;

Database altered.

SQL>

and, voila!, it will now extend to 2200 M instead of the 2000 M we originally set as its limit. This won’t succeed if you’re trying to set MAXSIZE smaller than the current file size and you have data that would be lost.

Of course one can always turn off autoextend:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend off;

Database altered.

SQL>

Over the years (and through many trenches) I’ve come to look at autoextend as a useful tool in a DBA’s belt, mostly because of improvements in storage technology and improvements in the autoextend mechanism. I don’t set all of the files in a tablespace to autoextend but having at least one configured to automatically expand can keep a busy DBA from receiving 2 AM calls because batch transasctions are failing due to a lack of space. Whether you use the filesystem or ASM to manage your storage autoextend can make DBA life easier (relatively speaking) so you can tackle that performance issue with the report which uses a 17-table, multi-million record join that absolutely, positively has to run in less than 2 minutes although it disables the use of every available index with function calls on indexed columns and a fistful of LIKE conditions which have wildcards on both ends. Did I forget to mention the ORDER BY?

At least, with autoextend, you might not run out of space in TEMP before it returns data.

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.

Blog at WordPress.com.