Quantcast
OracleBrains.Com header image 2

SQL Tip::UNION ALL Vs. UNION

September 11th, 2007 by Rajender Singh · No Comments

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
——————————————-

Tags: SQL and PL/SQL

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment