測試發現，Oracle不支援全外連線的查詢重寫，即使物化檢視和查詢語句完全匹配。

在10g中測試發現對於全外連線，建立語句一致的物化檢視也無法利用查詢重新功能：

[oracle@yans1 ~]$ sqlplus test/test

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

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。

查詢重寫對全外連線無效

相關文章