测试发现，Oracle不支持全外连接的查询重写，即使物化视图和查询语句完全匹配。

在10g中测试发现对于全外连接，创建语句一致的物化视图也无法利用查询重新功能：

[oracle@yans1 ~]$ sqlplus test/test

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 10月 18 12:16:47 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SET PAGES 100 LINES 120
SQL> SHOW PARAMETER QUERY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
SQL> CREATE MATERIALIZED VIEW MV_T1_FULL_JOIN_T2    
  2  ENABLE QUERY REWRITE AS
  3  SELECT T1.ID ID1, T2.ID ID2
  4  FROM T1 FULL OUTER JOIN T2
  5  ON T1.ID = T2.ID;

Materialized view created.

SQL> SET AUTOT ON
SQL> SELECT T1.ID ID1, T2.ID ID2
  2  FROM T1 FULL OUTER JOIN T2
  3  ON T1.ID = T2.ID;

       ID1        ID2
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         1
         0
                   10
                    9

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2841162349

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    10 |   260 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |      |    10 |   260 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     9 |   234 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |      |     1 |    26 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."ID"="T2"."ID"(+))
   6 - access("T1"."ID"="T2"."ID")

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

Statistics
----------------------------------------------------------
       1551  recursive calls
          0  db block gets
        427  consistent gets
         10  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         47  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> SELECT /*+ REWRITE(MV_T1_FULL_JOIN_T2) */ T1.ID ID1, T2.ID ID2
  2  FROM T1 FULL OUTER JOIN T2
  3  ON T1.ID = T2.ID;

       ID1        ID2
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         1
         0
                   10
                    9

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2841162349

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    10 |   260 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |      |    10 |   260 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     9 |   234 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |      |     1 |    26 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."ID"="T2"."ID"(+))
   6 - access("T1"."ID"="T2"."ID")

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

Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         61  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         11  rows processed

同样在11g中存在同样的问题，创建REWRITE_TABLE，检查无法查询重写的原因：

SQL> @?/rdbms/admin/utlxrw

表已创建。

SQL> begin
  2  dbms_mview.explain_rewrite('SELECT T1.ID, T2.ID
  3  FROM T1 FULL OUTER JOIN T2
  4  ON T1.ID = T2.ID');
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL> select MESSAGE from REWRITE_TABLE;

MESSAGE
-------------------------------------------------------------------------------------------
QSM-01150: 未重写查询
QSM-01219: 未找到合适的实体化视图来重写此查询
QSM-01263: 如果查询引用了字典表或视图, 则无法执行查询重写

根据表中的信息查询metalink，虽然找到类似的bug，但是这个bug在11gr2中已经被修正，而当前的sql在11gr2中依然存在。看来又是一个隐藏的bug。