union 与union all

union多个子查询结果集的集合，做去重处理。
union all多个子查询结果集的集合，不做去重处理。

SQL> create table tab1 as select * from user_objects;

Table created.
SQL> create table tab2 as select * from user_objects;

Table created.

SQL>set autot traceonly;

select * from tab1 union
select * from tab2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1398582079

[@more@]

------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45909 | 7173K| | 1764 (45)| 00:00:22 |
| 1 | SORT UNIQUE | | 45909 | 7173K| 16M| 1764 (45)| 00:00:22 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS FULL| TAB1 | 26004 | 4063K| | 69 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB2 | 19905 | 3110K| | 69 (2)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
616 consistent gets
0 physical reads
0 redo size
1712183 bytes sent via SQL*Net to client
17497 bytes received via SQL*Net from client
1550 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
23226 rows processed

select * from tab1 union all
select * from tab2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2207119028

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45909 | 7173K| 138 (51)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| TAB1 | 26004 | 4063K| 69 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TAB2 | 19905 | 3110K| 69 (2)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
3812 consistent gets
0 physical reads
0 redo size
2364191 bytes sent via SQL*Net to client
34525 bytes received via SQL*Net from client
3098 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
46451 rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22927800/viewspace-1029375/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22927800/viewspace-1029375/