oracle外连接失效,Oracle查询重写对全外连接无效（二）

测试发现，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中仍然没有完全解决。