Oracle技术之Oracle查询重写对全外连接无效(一)
测试发现，Oracle不支持全外连接的查询重写，即使物化视图和查询语句完全匹配。在10g中测试发现对于全外连接，创建语句一致的物化视图也无法利用查询重新功能：
$ 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
2ENABLE QUERY REWRITE AS
3SELECT T1.ID> 4FROM T1 FULL OUTER JOIN T2
5ON T1.ID = T2.ID;
Materialized view created.
SQL> SET AUTOT ON
SQL> SELECT T1.ID> 2FROM T1 FULL OUTER JOIN T2
3ON 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
----------------------------------------------------------
1551recursive calls
0db block gets
427consistent gets
10physical reads
0redo> 720bytes sent via SQL*Net to client
492bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
47sorts (memory)
0sorts (disk)
11rows processed
SQL> SELECT /*+ REWRITE(MV_T1_FULL_JOIN_T2) */ T1.ID> 2FROM T1 FULL OUTER JOIN T2
3ON 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
----------------------------------------------------------
13recursive calls
0db block gets
61consistent gets
0physical reads
0redo> 720bytes sent via SQL*Net to client
492bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
4sorts (memory)
0sorts (disk)
11rows processed
同样在11g中存在同样的问题，创建REWRITE_TABLE，检查无法查询重写的原因：
SQL> @?/rdbms/admin/utlxrw
表已创建。
SQL> begin
2dbms_mview.explain_rewrite('SELECT T1.ID, T2.ID
3FROM T1 FULL OUTER JOIN T2
4ON T1.ID = T2.ID');
5end;
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
页:
[1]