Oracle技术之Oracle查询重写对全外连接无效(一)

xywuyiba8

　　测试发现，Oracle不支持全外连接的查询重写，即使物化视图和查询语句完全匹配。
　　在10g中测试发现对于全外连接，创建语句一致的物化视图也无法利用查询重新功能：
　　[oracle@yans1 ~]$ sqlplus test/test

　　SQL*Plus:>　　Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
　　Connected to:

　　Oracle Database10gEnterprise Edition>　　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>　　4  FROM T1 FULL OUTER JOIN T2
　　5  ON T1.ID = T2.ID;
　　Materialized view created.
　　SQL> SET AUTOT ON

　　SQL> SELECT T1.ID>　　2  FROM T1 FULL OUTER JOIN T2
　　3  ON T1.ID = T2.ID;

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

　　|>　　-----------------------------------------------------------------------------
　　|   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>　　---------------------------------------------------
　　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>　　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>　　2  FROM T1 FULL OUTER JOIN T2
　　3  ON T1.ID = T2.ID;

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

　　|>　　-----------------------------------------------------------------------------
　　|   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>　　---------------------------------------------------
　　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>　　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。
　　oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html