在我们的规范里有一条：避免使用不必要的外连接，什么是不必要的外连接，为什么要避免呢
下面通过一个例子具体说明一下

create table test_obj1 as select * from dba_objects;
create table test_obj_s as select * from test_obj1 where rownum <100;
create table test_obj_s1 as select * from test_obj1 where rownum <10;
SQL> select /*+ optimizer_features_enable('9.2.0')*/
  2   a.object_name, b.object_type
  3    from test_obj1 a, test_obj_s b
  4   where a.object_id = b.object_id(+)
  5       and b.owner ='A';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 545394679

-----------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    38 |    13 |
|*  1 |  HASH JOIN         |            |     1 |    38 |    13 |
|*  2 |   TABLE ACCESS FULL| TEST_OBJ_S |     1 |    14 |     3 |
|   3 |   TABLE ACCESS FULL| TEST_OBJ1  | 13176 |   308K|     9 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."OWNER"='A')
   
能够读懂执行计划的人应该都清楚，这里CBO并没有按照外连接的方式去执行，而是将其转换为了常规的内连接
没错，是这样的，这样的外连接再加上 b.owner ='A'的条件，我们都能知道外连接是没有意义的了，oracle自然也清楚这一点，于是它主动去做了转换
当然，跟其他新增特性一样，oracle也提供了一个开关来控制这个行为   

ORA-10075: CBO Disable outer-join to regular join conversion

我们来试试：
SQL> alter session set events '10075 trace name context forever';

会话已更改。

已用时间:  00: 00: 00.01
SQL>  select /*+ optimizer_features_enable('9.2.0')*/
  2    a.object_name, b.object_type
  3     from test_obj1 a, test_obj_s b
  4    where a.object_id = b.object_id(+)
  5        and b.owner ='A';

未选定行

已用时间:  00: 00: 00.06

执行计划
----------------------------------------------------------
Plan hash value: 1995423895

------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            | 13176 |   488K|    15 |
|*  1 |  FILTER             |            |       |       |       |
|*  2 |   HASH JOIN OUTER   |            | 13176 |   488K|    15 |
|   3 |    TABLE ACCESS FULL| TEST_OBJ1  | 13176 |   308K|     9 |
|   4 |    TABLE ACCESS FULL| TEST_OBJ_S |    99 |  1386 |     2 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"."OWNER"='A')
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
看到了吧，outer-join to regular join conversion 被禁用，hash join outer ，并且大表作为了驱动表

看起来oracle已经很好的处理了这个问题，但会不会就像http://www.itpub.net/thread-1333630-1-5.html里
提到的那样，oracle的优化是针对场景的呢？

不幸的是，事实就是那样：         

     
SQL>  select /*+ optimizer_features_enable('9.2.0')*/
  2   a.object_name, b.object_type
  3    from test_obj1 a, test_obj_s b
  4   where a.object_id = b.object_id(+)
  5         and b.owner in ('A','B');

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1995423895

------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            | 13171 |   488K|    15 |
|*  1 |  FILTER             |            |       |       |       |
|*  2 |   HASH JOIN OUTER   |            | 13171 |   488K|    15 |
|   3 |    TABLE ACCESS FULL| TEST_OBJ1  | 13171 |   308K|     9 |
|   4 |    TABLE ACCESS FULL| TEST_OBJ_S |     2 |    28 |     2 |
------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        179  consistent gets
          0  physical reads
          0  redo size
         

SQL> select /*+ optimizer_features_enable('9.2.0')*/
  2  a.object_name, b.object_type
  3   from test_obj1 a, test_obj_s b
  4  where a.object_id = b.object_id(+)
  5        and( b.owner ='A'
  6             or b.owner='B');

未选定行

已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1995423895

------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            | 13176 |   488K|    15 |
|*  1 |  FILTER             |            |       |       |       |
|*  2 |   HASH JOIN OUTER   |            | 13176 |   488K|    15 |
|   3 |    TABLE ACCESS FULL| TEST_OBJ1  | 13176 |   308K|     9 |
|   4 |    TABLE ACCESS FULL| TEST_OBJ_S |    99 |  1386 |     2 |
------------------------------------------------------------------

                                                            
                                                                  
SQL> select /*+ optimizer_features_enable('9.2.0')*/              
  2   a.object_name, b.object_type                                
  3    from test_obj1 a, test_obj_s b
  4   where a.object_id = b.object_id(+)
  5       and b.owner in (select 'A' from dual);

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 2808197116

---------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            | 13171 |   527K|    20 |
|*  1 |  FILTER                |            |       |       |       |
|*  2 |   HASH JOIN OUTER      |            | 13171 |   527K|    20 |
|   3 |    MERGE JOIN CARTESIAN|            | 13171 |   347K|    13 |
|   4 |     VIEW               | VW_NSO_1   |     1 |     3 |     4 |
|   5 |      FAST DUAL         |            |     1 |       |     2 |
|   6 |     TABLE ACCESS FULL  | TEST_OBJ1  | 13171 |   308K|     9 |
|   7 |    TABLE ACCESS FULL   | TEST_OBJ_S |     2 |    28 |     2 |
---------------------------------------------------------------------

SQL> select /*+ optimizer_features_enable('9.2.0')*/
  2  a.object_name, b.object_type
  3   from test_obj1 a, test_obj_s b
  4  where a.object_id = b.object_id(+)
  5        and b.owner in (select owner from test_obj_s1);

已选择99行。


执行计划
----------------------------------------------------------
Plan hash value: 1592258223

----------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             | 13176 |   540K|    20 |
|*  1 |  FILTER                |             |       |       |       |
|*  2 |   HASH JOIN OUTER      |             | 13176 |   540K|    20 |
|   3 |    MERGE JOIN CARTESIAN|             | 13176 |   360K|    13 |
|   4 |     SORT UNIQUE        |             |     9 |    36 |     2 |
|   5 |      TABLE ACCESS FULL | TEST_OBJ_S1 |     9 |    36 |     2 |
|   6 |     BUFFER SORT        |             | 13176 |   308K|    11 |
|   7 |      TABLE ACCESS FULL | TEST_OBJ1   | 13176 |   308K|     9 |
|   8 |    TABLE ACCESS FULL   | TEST_OBJ_S  |    99 |  1386 |     2 |
----------------------------------------------------------------------

上面3种方式在oracle release9均不能正确的转换，甚至在b上出现嵌套子查询时选择了merge join cartesian，不容置疑后果会很惨。。。

生产环境上一个一个的事实让我们不得不一次又一次强调避免不必要的外连接这一规范

以此同时，ORACLE也没闲着

到了10G，oracle意识到这个问题
         
SQL>  select /*+ optimizer_features_enable('10.2.0.4')*/                             
  2   a.object_name, b.object_type                                                   
  3    from test_obj1 a, test_obj_s b                                                
  4   where a.object_id = b.object_id(+)                                             
  5         and b.owner in ('A','B');                                                
                                                                                    
未选定行                                                                             
                                                                                    
已用时间:  00: 00: 00.06                                                            
                                                                                    
执行计划                                                                             
----------------------------------------------------------                           
Plan hash value: 3439204987                                                         
                                                                                    
-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |   133 |  5054 |    39   (3)| 00:00:01 |
|*  1 |  FILTER                |            |       |       |            |          |
|*  2 |   HASH JOIN RIGHT OUTER|            |   133 |  5054 |    39   (3)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | TEST_OBJ_S |    99 |  1386 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | TEST_OBJ1  | 13176 |   308K|    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

虽然没有转换成inner join，但换成了right outer join 驱动表发生了变化

如果是in子句的方式却能够转换成inner join了（可能这个场景下糟糕的执行计划在各个客户的环境下表现过于突出反而促使oracle优先处理了，呵呵）
如下：
SQL> select /*+ optimizer_features_enable('10.2.0.4')*/
  2  a.object_name, b.object_type
  3   from test_obj1 a, test_obj_s b
  4  where a.object_id = b.object_id(+)
  5        and b.owner in (select owner from test_obj_s1);

已选择99行。


执行计划
----------------------------------------------------------
Plan hash value: 3434607345

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    99 |  4158 |    41   (3)| 00:00:01 |
|*  1 |  HASH JOIN SEMI     |             |    99 |  4158 |    41   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |             |    99 |  3762 |    39   (3)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST_OBJ_S  |    99 |  1386 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST_OBJ1   | 13176 |   308K|    35   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | TEST_OBJ_S1 |     9 |    36 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

而到了11G，至少，这几个场景都能够正确的改写了

SQL>  select /*+ optimizer_features_enable('11.2.0.1')*/
  2   a.object_name, b.object_type
  3    from test_obj1 a, test_obj_s b
  4   where a.object_id = b.object_id(+)
  5         and b.owner in ('A','B');

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 545394679

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    38 |    39   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |            |     1 |    38 |    39   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST_OBJ_S |     1 |    14 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_OBJ1  | 13176 |   308K|    35   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."OWNER"='A' OR "B"."OWNER"='B')

OR的情况就不用看了，从上面predicate information 的情况也能看出，肯定也是没问题了

上面的案例，数据量大，且a.object_id有索引的情况下，实际执行效果对比会非常明显