Advanced Oracle SQL: Set Operators Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

 

1.    UNION ALL

UNION ALL selects all rows from all queries:

SELECT
   1 N
FROM
   DUAL
UNION ALL
SELECT
   2 N
FROM
   DUAL
UNION ALL
SELECT
   2 N
FROM
   DUAL; 

         N
----------
         1
         2
         2

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |     6  (67)| 00:00:01 |
|   1 |  UNION-ALL       |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   3 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   4 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

All rows are returned. Each query must have the same number of columns and the datatypes must be the same or compatible.

The partition views feature (Oracle 7.3) does not allow inserts or updates and the partitioning is done manually.

-- Id     : $Id: partview.sql,v 1.3 2015/01/18 17:16:30 lsc Exp $
-- Author : $Author: lsc $
-- Date   : $Date: 2015/01/18 17:16:30 $
--
-- Create EMP10 to EMP40 in current schema
-- 

WHENEVER SQLERROR EXIT 

EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP10'; EXCEPTION WHEN OTHERS THEN NULL
EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP20'; EXCEPTION WHEN OTHERS THEN NULL
EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP30'; EXCEPTION WHEN OTHERS THEN NULL
EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP40'; EXCEPTION WHEN OTHERS THEN NULL 

CREATE TABLE
   EMP10
AS SELECT
   *
FROM
   EMP
WHERE
   DEPTNO=10
/
CREATE TABLE
   EMP20
AS SELECT
   *
FROM
   EMP
WHERE
   DEPTNO=20
/
CREATE TABLE
   EMP30
AS SELECT
   *
FROM
   EMP
WHERE
   DEPTNO=30
/
CREATE TABLE
   EMP40
AS SELECT
   *
FROM
   EMP
WHERE
   DEPTNO=40
/
ALTER TABLE
   EMP10
ADD CONSTRAINT
   EMP10_PK
PRIMARY KEY
   (EMPNO)
/
ALTER TABLE
   EMP10
ADD CONSTRAINT
   CHECK10
CHECK
   (DEPTNO=10)
/
ALTER TABLE
   EMP20
ADD CONSTRAINT
   EMP20_PK
PRIMARY KEY
   (EMPNO)
/
ALTER TABLE
   EMP20
ADD CONSTRAINT
   CHECK20
CHECK
   (DEPTNO=20)
/
ALTER TABLE
   EMP30
ADD CONSTRAINT
   EMP30_PK
PRIMARY KEY
   (EMPNO)
/
ALTER TABLE
   EMP30
ADD CONSTRAINT
   CHECK30
CHECK
   (DEPTNO=30)
/
ALTER TABLE
   EMP40
ADD CONSTRAINT
   EMP40_PK
PRIMARY KEY
   (EMPNO)
/
ALTER TABLE
   EMP40
ADD CONSTRAINT
   CHECK40
CHECK
   (DEPTNO=40)
/
CREATE OR REPLACE VIEW
   V_EMP
AS
   SELECT
      *
   FROM
      EMP10
   UNION ALL
   SELECT
      *
   FROM
      EMP20
   UNION ALL
   SELECT
      *
   FROM
      EMP30
   UNION ALL
   SELECT
      *
   FROM
      EMP40
/ 

Four tables and a view are created. The optimizer recognizes this view as a partition view and chooses an efficient plan to retrieve data:

SELECT
   ENAME
FROM
   V_EMP
WHERE
   DEPTNO=20
   AND
   SAL>2000; 

ENAME
----------
JONES
SCOTT
FORD

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     4 |    56 |     3   (0)| 00:00:01 |
|   1 |  VIEW                | V_EMP |     4 |    56 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL PARTITION|       |       |       |            |          |
|*  3 |    FILTER            |       |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| EMP10 |     1 |    13 |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL | EMP20 |     2 |    26 |     2   (0)| 00:00:01 |
|*  6 |    FILTER            |       |       |       |            |          |
|*  7 |     TABLE ACCESS FULL| EMP30 |     1 |    14 |     2   (0)| 00:00:01 |
|*  8 |    FILTER            |       |       |       |            |          |
|*  9 |     TABLE ACCESS FULL| EMP40 |     1 |    33 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
   3 - filter(NULL IS NOT NULL)
   4 - filter("DEPTNO"=20 AND "SAL">2000)
   5 - filter("SAL">2000 AND "DEPTNO"=20)
   6 - filter(NULL IS NOT NULL)
   7 - filter("DEPTNO"=20 AND "SAL">2000)
   8 - filter(NULL IS NOT NULL)
   9 - filter("DEPTNO"=20 AND "SAL">2000)

Note the UNION-ALL PARTITION operation.

Table partitioning (Oracle 8.0 and later) is much more efficient.

-- Id     : $Id: parttable.sql,v 1.3 2015/01/18 17:52:53 lsc Exp $
-- Author : $Author: lsc $
-- Date   : $Date: 2015/01/18 17:52:53 $
--
-- Create EMP_PART in current schema
-- 

WHENEVER SQLERROR EXIT 

EXEC EXECUTE IMMEDIATE 'DROP TABLE EMP_PART'; EXCEPTION WHEN OTHERS THEN NULL 

CREATE TABLE
   EMP_PART
PARTITION BY LIST
(
   DEPTNO
)
(
   PARTITION EMP_P10 VALUES(10),
   PARTITION EMP_P20 VALUES(20),
   PARTITION EMP_P30 VALUES(30),
   PARTITION EMP_P40 VALUES(40)
)
AS SELECT
   *
FROM
   EMP
/
ALTER TABLE
   EMP_PART
ADD CONSTRAINT
   EMP_PART_PK
PRIMARY KEY
   (EMPNO)
/ 

Only one table is created with four partitions. The optimizer avoids accessing partitions:

SELECT
   ENAME
FROM
   EMP_PART
WHERE
   DEPTNO=20
   AND
   SAL>2000;

ENAME
----------
JONES
SCOTT
FORD
-------------------------------------------------------------------------------------------| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |          |     3 |    39 |     2   (0)| 00:00:01 |      
|       |
|   1 |  PARTITION LIST SINGLE|          |     3 |    39 |     2   (0)| 00:00:01 |     2
|     2 |
|*  2 |   TABLE ACCESS FULL   | EMP_PART |     3 |    39 |     2   (0)| 00:00:01 |     2
|     2 |
-------------------------------------------------------------------------------------------
   2 - filter("SAL">2000)

Only a single partition is accessed.

2.      UNION

The UNION set operator selects all rows and removes duplicates:

SELECT
   1 N
FROM
   DUAL
UNION
SELECT
   2 N
FROM
   DUAL
UNION
SELECT
   2 N
FROM
   DUAL; 

         N
----------
         1
         2

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |     9  (78)| 00:00:01 |
|   1 |  SORT UNIQUE     |      |     3 |     9  (78)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |            |          |
|   3 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
|   5 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

All unique rows are returned. The UNION is shown actually doing a UNION ALL operation before removing the duplicates with a SORT UNIQUE. Due to that additional operation, UNION ALL performs better.

3.      MINUS

The MINUS set operator selects from the first query the rows that are not in the second query and removes any duplicates:

SELECT
   DEPTNO
FROM
   DEPT
MINUS
SELECT
   DEPTNO
FROM
   EMP;
 

    DEPTNO
----------
        40

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     4 |    54 |     5  (80)| 00:00:01 |
|   1 |  MINUS              |         |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|         |     4 |    12 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |         |    14 |    42 |     3  (34)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Only department 40 remains because the other ones exist in EMP.

4.      INTERSECT

INTERSECT selects the distinct rows from the queries:

SELECT
   DEPTNO
FROM
   EMP
INTERSECT
SELECT
   DEPTNO
FROM
   DEPT;
    DEPTNO
----------
        10
        20
        30

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     4 |    54 |     5  (60)| 00:00:01 |
|   1 |  INTERSECTION       |         |       |       |            |          |
|   2 |   SORT UNIQUE       |         |    14 |    42 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP     |    14 |    42 |     2   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|         |     4 |    12 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Departments 10, 20 and 30 are in both tables. Intersect is particularly useful when a very large number of columns must be compared. This way, the optimizer does not have to figure out how to join the tables and the parsing can be quicker.