Why Oracle query overrides are not valid for full outer joins

The trial found that Oracle does not support full outer join query rewriting, even if materialized views and query statements match exactly.

In the 10g test found that for all outer joins, the creation of a statement-consistent materialized view also failed to take advantage of query re-functionality:

[Oracle@yans1 ~]$ Sqlplus test/test

Sql*plus:release10.2.0.3.0-production on Monday October 18 12:16:47 2010

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

Connected to:

Oracle database10genterprise Edition release10.2.0.3.0-64bit Production

With the partitioning, OLAP and Data Mining options

sql> SET PAGES 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

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 |

This column more highlights: http://www.bianceng.cn/database/Oracle/

|  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 |