oracle的left join，right join和full join的一点介绍(三)
cs
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
union的执行计划：
hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+)
2 union www.2cto.com
3 select a.id,b.id from a,b where a.id(+)=b.id;
ID ID
---------- ----------
1 1
2 2
3
4
Execution Plan
----------------------------------------------------------
Plan hash value: 891669117
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 156 | 15 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 156 | 15 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 78 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 3 | 78 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 39 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 39 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"(+)="B"."ID")
Note
-----
- dynamic sampling used for this statement
Statistics www.2cto.com
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
显然，union连接的cpu代价要比full join连接来得多。此外，union还会暗含一个排序操作。当数据量海量时，估计会对性能带来一定的影响。而且，在oracle的私有语法里，是没有全外连接的，只能通过union连接来模拟full join。所以，建议需要使用外连接时，请使用full join，不要用union模拟。
作者 linwaterbin