测试发现,Oracle不支持全外连接的查询重写,即使物化视图和查询语句完全匹配。
在10g中测试发现对于全外连接,创建语句一致的物化视图也无法利用查询重新功能:
[oracle@yans1 ~]$ sqlplus test/test
SQL*Plus: Release10.2.0.3.0 - Production on星期一10月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 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。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
转载于:https://blog.51cto.com/19880614/1224041