The problem with UNION is that it required sorting operation to eliminate duplicate row sets which is added cost for executing a statement, while UNION ALL return all rows without any sorting or duplication checking.

If duplicate rows are not important, using UNION ALL can save cost in terms of expensive sorts, merge and filtering operation.

Example:

SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘SA’
UNION
SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘TM’

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
——————————————–
Plan hash value: 2107484601

——————————————–
| Id | Operation | Name
——————————————–
| 0 | SELECT STATEMENT |
| 1 | SORT UNIQUE |
| 2 | UNION-ALL |
|* 3 | TABLE ACCESS FULL| DEPT_EXPENSES
|* 4 | TABLE ACCESS FULL| DEPT_EXPENSES
——————————————–

SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘SA’
UNION ALL
SELECT dept_code, expense_code, amount
FROM dept_expenses
WHERE location_code = ‘TM’

After running the explain plan for above statement it shows me:

PLAN_TABLE_OUTPUT
——————————————-
Plan hash value: 576826761

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