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.
"Event"-ually
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.
"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.
Over Extended
Autoextend can be very helpful to busy DBAs by allowing Oracle to automatically increase file size when necessary. Some DBAs swear by it. Others swear at it, as it can be one of the largest nightmares a DBA can experience when left to its own devices by not providing a maximum size the file should attain.
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:
While it may be considered wise by some to set datafiles to autoextend, doing so without first restricting their size is a grievous error. Executing the statement shown below:
On leveraged systems (systems where more than one database may reside) autoextend, in any form, isn't a wise idea as you now have multiple databases competing for limited resources (disk space) and whichever database gets to that available space first, wins. And you also have X times more opportunities to reach the end of the physical media, and you can do it so much faster when 13 databases are competing for disk space on the same devices.
Okay, so you have autoextend datafiles and can't change that fact (management says 'We need them'); how do you monitor the size? Hopefully a MAXSIZE was set and, if so, it's pretty easy. The DBA_DATA_FILES view provides almost all of the necessary information (what's missing is the db_block_size, and that's fairly easy to find):
Can you set a datafile, 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:
Of course one can always turn off autoextend:
As I learned early on, don't over-extend yourself; it can only get you into trouble later.
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>
And, not every datafile in a tablespace needs to be set to autoextend -- you can select one or more datafiles and leave others unaltered. The only true physical limit for autoextend datafiles is the size of the file system where they reside. Which is where the 'swearing at autoextend' part of the discussion begins.While it may be considered wise by some to set datafiles to autoextend, doing so without first restricting their size is a grievous error. Executing the statement shown below:
SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on;
Database altered.
SQL>
is, well, irresponsible and careless, as the only restriction you've provided Oracle for the file size is the edge of the disk (figuratively speaking). Oracle will continue to extend this datafile ad infinitum, that is until it reaches the physical end of the storage media, when the process, and likely the database, will come to a grinding halt, displaying the (possibly nauseating) error:
ORA-01237: cannot extend datafile /whackenhut/smackdoodle/endoplasm/flurst01.dbf
followed by the relevant operating system errors indicating you have no space left on the desired device. And, by now, you have corrupted data in this tablespace and your tablespace (or, worse, database, depending upon how widespread this corruption becomes) needs to be restored and recovered. Yes, it (hopefully) ensures your backup and recovery strategy is sound, but I'm fairly certain that management wasn't expecting to test that process by having datafiles attempt to extend beyond their physical limits.On leveraged systems (systems where more than one database may reside) autoextend, in any form, isn't a wise idea as you now have multiple databases competing for limited resources (disk space) and whichever database gets to that available space first, wins. And you also have X times more opportunities to reach the end of the physical media, and you can do it so much faster when 13 databases are competing for disk space on the same devices.
Okay, so you have autoextend datafiles and can't change that fact (management says 'We need them'); how do you monitor the size? Hopefully a MAXSIZE was set and, if so, it's pretty easy. The DBA_DATA_FILES view provides 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>
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 to ensure that you don't autoextend yourself out of a database.Can you set a datafile, 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.Of course one can always turn off autoextend:
SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend off;
Database altered.
SQL>
On the whole I cannot recommend to anyone the use of autoextend for datafiles. Others may disagree, and that's what makes this such an interesting world in which to live. It's your choice to make; the decision should be a wise one, not one of convenience. I'd prefer to be 'inconvenienced' by a monitoring tool or script rather than the necessity of restoring a tablespace, or a database, because autoextend was implemented and left unrestricted.As I learned early on, don't over-extend yourself; it can only get you into trouble later.
Preserving The Union
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:
We can take a previous example and complicate it further to make it work:
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.
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.
Subscribe to:
Posts (Atom)
