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

继续测试对外连接物化视图是否可以重写。



上一篇测试发现即使查询完全匹配，全外连接也无法利用物化视图进行查询重写，那么问题是FULL OUTER JOIN这种写法导致的，还是外连接本身的特殊性导致的。

SQL> SET AUTOT ON

SQL> SELECT T1.ID, T2.ID

 2  FROM T1, T2

 3  WHERE T1.ID = T2.ID (+);

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

        1

        0

已选择9行。

执行计划

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

Plan hash value: 1823443478

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     9 |   234 |     9  (12)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     9 |   234 |     9  (12)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   |     9 |   117 |     4   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |     9 |   117 |     4   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  1 - access("T1"."ID"="T2"."ID"(+))

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

         0  recursive calls

         0  db block gets

        15  consistent gets

         0  physical reads

         0  redo size

       716  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         9  rows processed

SQL> CREATE MATERIALIZED VIEW MV_T12

 2  ENABLE QUERY REWRITE AS

 3  SELECT T1.ID ID1, T2.ID ID2

 4  FROM T1, T2

 5  WHERE T1.ID = T2.ID (+);

实体化视图已创建。

SQL> SELECT T1.ID, T2.ID

 2  FROM T1, T2

 3  WHERE T1.ID = T2.ID (+);

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

        1

        0

已选择9行。


执行计划

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

Plan hash value: 183449623

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |        |     9 |   234 |     3   (0)| 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T12 |     9 |   234 |     3   (0)| 00:00:01 |

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

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

        13  recursive calls

         0  db block gets

        24  consistent gets

         1  physical reads

         0  redo size

       723  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         9  rows processed

对于普通外连接是可以进行查询重写的，检查是否是SQL标准语法导致的问题：

SQL> SELECT T1.ID, T2.ID

 2  FROM T1 LEFT OUTER JOIN T2

 3  ON T1.ID = T2.ID;

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

        1

        0

已选择9行。


执行计划

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

Plan hash value: 183449623

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

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |        |     9 |   234 |     3   (0)| 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T12 |     9 |   234 |     3   (0)| 00:00:01 |

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

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

        10  recursive calls

         0  db block gets

        24  consistent gets

         0  physical reads

         0  redo size

       723  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         9  rows processed

和标准语法也没有关系，检查全外连接能否使用这个物化视图进行查询重写：

SQL> SELECT T1.ID, T2.ID

 2  FROM T1 FULL OUTER JOIN T2

 3  ON T1.ID = T2.ID;

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

                   9

                  10

        1

        0

已选择11行。


执行计划

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

Plan hash value: 53297166

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

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |          |     9 |   234 |     9  (12)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 |     9 |   234 |     9  (12)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     9 |   234 |     9  (12)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | T1       |     9 |   117 |     4   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T2       |     9 |   117 |     4   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("T1"."ID"="T2"."ID")

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

         7  recursive calls

         0  db block gets

        31  consistent gets

         0  physical reads

         0  redo size

       733  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        11  rows processed

SQL> SELECT /*+ REWRITE (MV_T12) */ T1.ID, T2.ID

 2  FROM T1 FULL OUTER JOIN T2

 3  ON T1.ID = T2.ID;

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

                   9

                  10

        1

        0

已选择11行。


执行计划

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

Plan hash value: 53297166

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

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      |          |     9 |   234 |     9  (12)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 |     9 |   234 |     9  (12)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     9 |   234 |     9  (12)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | T1       |     9 |   117 |     4   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | T2       |     9 |   117 |     4   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("T1"."ID"="T2"."ID")

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

         7  recursive calls

         0  db block gets

        31  consistent gets

         0  physical reads

         0  redo size

       733  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        11  rows processed

SQL> SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */ T1.ID, T2.ID

 2  FROM T1 FULL OUTER JOIN T2

 3  ON T1.ID = T2.ID;

       ID        ID

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

        2          2

        3          3

        4          4

        5          5

        6          6

        7          7

        8          8

        1

        0

                  10

                   9

已选择11行。


执行计划

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

Plan hash value: 665903394

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

| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT               |        |    18 |   468 |    12   (9)| 00:00:01 |

|   1 |  VIEW                          |        |    18 |   468 |    12   (9)| 00:00:01 |

|   2 |   UNION-ALL                    |        |       |       |            |          |

|   3 |    MAT_VIEW REWRITE ACCESS FULL| MV_T12 |     9 |   234 |     3   (0)| 00:00:01 |

|*  4 |    HASH JOIN ANTI              |        |     9 |   234 |     9  (12)| 00:00:01 |

|   5 |     TABLE ACCESS FULL          | T2     |     9 |   117 |     4   (0)| 00:00:01 |

|   6 |     TABLE ACCESS FULL          | T1     |     9 |   117 |     4   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  4 - access("T1"."ID"="T2"."ID")

Note

-----

  - dynamic sampling used for this statement (level=2)


统计信息

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

        22  recursive calls

         0  db block gets

        70  consistent gets

         0  physical reads

         0  redo size

       733  bytes sent via SQL*Net to client

       520  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        11  rows processed

直接利用REWRITE提示，Oracle不会利用物化视图进行重新，但是如果利用NO_NATIVE_FULL_OUTER_JOIN提示，那么Oracle会利用创建的物化视图进行查询重写。

看来问题和metalink文档Bug 7032300描述的现象基本一致，看来这个bug在11.2中仍然没有完全解决。