UNION vs UNION ALL - Oracle Performance Tuning

 

Simple Understanding First

UNION and UNION ALL both combine results from multiple queries.

Difference:

Operator	What it does
UNION	Combines data AND removes duplicates
UNION ALL	Combines data WITHOUT removing duplicates

Because UNION removes duplicates, Oracle must do an additional SORT UNIQUE operation.

That sorting consumes:

• CPU
• TEMP tablespace
• Memory
• Time

So if duplicate removal is NOT needed, always use UNION ALL.

---

Step-by-Step Example Using EMP Table

Assume classic EMP table:

EMPNO	ENAME	DEPTNO	SAL
7369	SMITH	20	800
7499	ALLEN	30	1600
7521	WARD	30	1250
7566	JONES	20	2975
7698	BLAKE	30	2850
7782	CLARK	10	2450
7839	KING	10	5000

---

Scenario 1 — Using UNION

Suppose developer writes:

SELECT empno, ename
FROM emp
WHERE deptno = 10
UNION
SELECT empno, ename
FROM emp
WHERE deptno = 20;

---

What Oracle Does Internally

Step 1

Fetch rows from DEPT 10

7782 CLARK
7839 KING

Step 2

Fetch rows from DEPT 20

7369 SMITH
7566 JONES

Step 3

Oracle now thinks:

"Maybe duplicate rows exist."

So it performs:

SORT UNIQUE

to remove duplicates.

Even though duplicates DO NOT exist.

---

Explain Plan for UNION

EXPLAIN PLAN FOR
SELECT empno, ename
FROM emp
WHERE deptno = 10
UNION
SELECT empno, ename
FROM emp
WHERE deptno = 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Typical plan:

--------------------------------------------------------------------------------
| Id | Operation           | Name | 
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |      |
|  1 |  SORT UNIQUE        |      |
|  2 |   UNION-ALL         |      |
|  3 |    TABLE ACCESS FULL| EMP  |
|  4 |    TABLE ACCESS FULL| EMP  |
--------------------------------------------------------------------------------

---

Layman Meaning of SORT UNIQUE

Imagine:

You collected student names from:

• Class A
• Class B

Now principal says:

"Remove duplicate names."

So you:

1. Put all names together
2. Sort them
3. Compare each row
4. Remove duplicates

This takes extra work.

That is exactly what Oracle does in UNION.

---

Scenario 2 — Using UNION ALL

Now query:

SELECT empno, ename
FROM emp
WHERE deptno = 10
UNION ALL
SELECT empno, ename
FROM emp
WHERE deptno = 20;

---

What Oracle Does

Step 1

Fetch rows from DEPT 10

Step 2

Fetch rows from DEPT 20

Step 3

Return directly.

NO sorting.
NO duplicate checking.

Much faster.

---

Explain Plan for UNION ALL

EXPLAIN PLAN FOR
SELECT empno, ename
FROM emp
WHERE deptno = 10
UNION ALL
SELECT empno, ename
FROM emp
WHERE deptno = 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan:

--------------------------------------------------------------------------------
| Id | Operation           | Name |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |      |
|  1 |  UNION-ALL          |      |
|  2 |   TABLE ACCESS FULL | EMP  |
|  3 |   TABLE ACCESS FULL | EMP  |
--------------------------------------------------------------------------------

Notice:

SORT UNIQUE

is gone.

This is the performance gain.

---

Real Performance Difference

Suppose:

Query	Rows	Time
UNION	10 million	45 sec
UNION ALL	10 million	12 sec

Why?

Because sorting huge data is expensive.

---

Scenario 3 — When UNION is REALLY Needed

Suppose:

SELECT deptno
FROM emp
UNION
SELECT deptno
FROM emp;

Result:

10
20
30

Duplicates removed.

Here UNION makes sense.

---

Scenario 4 — Dangerous Use of UNION ALL

SELECT deptno
FROM emp
UNION ALL
SELECT deptno
FROM emp;

Output:

10
20
30
10
20
30

Duplicates appear.

So use UNION ALL only when duplicates are acceptable OR impossible.

---

Oracle DBA Interview Logic

Interviewer may ask:

Why is UNION slower?

Answer:

Because UNION performs duplicate elimination using SORT UNIQUE operation,
which increases CPU, memory, TEMP usage and execution time.
UNION ALL avoids this extra sorting operation.

---

Easy Memory Trick

UNION = Combine + Remove duplicates + Sorting

UNION ALL = Just Combine

---

Production Scenario

Bad query:

SELECT * FROM orders_2024
UNION
SELECT * FROM orders_2025;

If order IDs are already unique:

• duplicate removal unnecessary
• wasting resources

Better:

SELECT * FROM orders_2024
UNION ALL
SELECT * FROM orders_2025;

Huge performance improvement in production systems.

---

Final Golden Rule

Use: UNION ALL by default.

Use: UNION , ONLY when duplicate removal is required.