SQL Tuning - UNION ALL versus UNION

SQL Tuning - UNION ALL versus UNION :


Programmers of complex query statements that include a UNION clause should always ask whether a UNION ALL will suffice. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned to the calling module. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge, or filter.

Most statements that do include a UNION clause can in fact replace it with a UNION ALL. These statements are written in a fashion whereby duplicates cannot be generated (sourced from different tables), so why sort and test for them?

Consider the following example:

SELECT acct_num, balance_amt
FROM   debit_transactions
WHERE  tran_date = '31-DEC-95'
UNION
SELECT acct_num, balance_amt
FROM   credit_transactions
WHERE  tran_date = '31-DEC-95'
Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
PROJECTION
SORT (UNIQUE)
UNION-ALL
TABLE ACCESS (BY ROWID) OF 'DEBIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'DEBIT_TRAN_IDX' (NON-UNIQUE)
TABLE ACCESS (BY ROWID) OF 'CREDIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'CREDIT_TRAN_IDX' (NON-UNIQUE)
To improve performance, replace this code with:

SELECT acct_num, balance_amt
FROM   debit_transactions
WHERE  tran_date = '31-DEC-95'
UNION ALL
SELECT acct_num, balance_amt
FROM   credit_transactions
WHERE  tran_date = '31-DEC-95'
Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
PROJECTION
UNION-ALL
TABLE ACCESS (BY ROWID) OF 'DEBIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'DEBIT_TRAN_IDX' (NON-UNIQUE)
TABLE ACCESS (BY ROWID) OF 'CREDIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'CREDIT_TRAN_IDX' (NON-UNIQUE)