降级优化器到10.2
SQL> alter system set optimizer_features_enable ='10.2.0.2' scope=memory ;
System altered.
执行全外连接语句
SQL> set autotrace trace
SQL> set pagesize 200
SQL> set linesize 200
SQL> select *
2 from ESTIMATES e
3 full outer join ACTUALS a on e.ITEM_NBR = a.ITEM_NBR ;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2047377711
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 406 | 13 (8)| 00:00:01 |
| 1 | VIEW | | 7 | 406 | 13 (8)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 6 | 108 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| ESTIMATES | 5 | 30 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| ACTUALS | 4 | 48 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN ANTI | | 1 | 15 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| ACTUALS | 4 | 48 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| ESTIMATES | 5 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."ITEM_NBR"="A"."ITEM_NBR"(+))
6 - access("E"."ITEM_NBR"="A"."ITEM_NBR")
Statistics
----------------------------------------------------------
33 recursive calls
4 db block gets
53 consistent gets
1 physical reads
0 redo size
988 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
8 rows processed
运行根据执行计划反写的SQL语句
SQL> select *
2 from ESTIMATES e, ACTUALS a
3 where e.ITEM_NBR = a.ITEM_NBR(+)
4 union all
5 select null, null, a.*
6 from ACTUALS a
7 where not exists (select 1 from ESTIMATES e where e.item_nbr = a.item_nbr);
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3616513639
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 123 | 13 (54)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN OUTER | | 6 | 108 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ESTIMATES | 5 | 30 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| ACTUALS | 4 | 48 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN ANTI | | 1 | 15 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL| ACTUALS | 4 | 48 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| ESTIMATES | 5 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."ITEM_NBR"="A"."ITEM_NBR"(+))
5 - access("E"."ITEM_NBR"="A"."ITEM_NBR")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
988 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
将优化器升级到11.2.0.2
SQL> alter system set optimizer_features_enable ='11.2.0.2' scope=memory ;
System altered.
再次执行全外连接语句
SQL> select *
2 from ESTIMATES e
3 full outer join ACTUALS a on e.ITEM_NBR = a.ITEM_NBR ;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1818351901
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 348 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 6 | 348 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 6 | 108 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | ACTUALS | 4 | 48 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | ESTIMATES | 5 | 30 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."ITEM_NBR"="A"."ITEM_NBR")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
997 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
总结：11g优化器对全外连接进行了加强，降低了执行计划复杂程度与索要消耗的资源。