Use UNION ALL instead of UNION:

CPU consumption are more when there is any SORT operation, so they are very expensive.
The UNOIN operation sorts the result which eliminate the rows within the sub-query or removes the duplicates from the result set.
Unless it is required to eliminate duplicate rows from the result set then it is preferred to use UNION ALL instead of UNION
The UNION ALL operation do not use SORT operation and include the duplicate rows.

Example using UNION operation:

SQL> explain plan for
select empno
from emp
UNION
select empno
from emp;

Explained.

Explain Plan Output:

PLAN_TABLE_OUTPUT
————————————————————
Plan hash value: 1159287242

—————————————————————————-
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT  |        |    28 |   112 |     4  (75)| 00:00:01 |
|   1 |  SORT UNIQUE      |        |    28 |   112 |     4  (75)| 00:00:01 |
|   2 |   UNION-ALL       |        |       |       |            |          |
|   3 |    INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
|   4 |    INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
—————————————————————————-

11 rows selected.

Example using UNION ALL operation:

SQL> explain plan for
select empno
from emp
UNION ALL
select empno
from emp;

Explain Plan Output:

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 2160192121

—————————————————————————
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT |        |    28 |   112 |     2  (50)| 00:00:01 |
|   1 |  UNION-ALL       |        |       |       |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
|   3 |   INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
—————————————————————————

10 rows selected.

From the above examples we can see the difference in the CPU COST is been reduced after using UNION ALL condition. So it is always preferred to use UNION ALL instead of UNION operation when we do not required to eliminate duplicates rows from our result set.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s