查詢重寫對全外連線無效（二）
測試發現，Oracle不支援全外連線的查詢重寫，即使物化檢視和查詢語句完全匹配。
查詢重寫對全外連線無效：http://yangtingkun.itpub.net/post/468/507283
繼續測試對外連線物化檢視是否可以重寫。
上一篇測試發現即使查詢完全匹配，全外連線也無法利用物化檢視進行查詢重寫，那麼問題是FULL OUTER JOIN這種寫法導致的，還是外連線本身的特殊性導致的。
SQL> SET AUTOT ON
SQL> SELECT T1.ID, T2.ID
2 FROM T1, T2
3 WHERE T1.ID = T2.ID (+);
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1
0
已選擇9行。
執行計劃
----------------------------------------------------------
Plan hash value: 1823443478
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 9 | 234 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 9 | 117 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 9 | 117 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
716 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> CREATE MATERIALIZED VIEW MV_T12
2 ENABLE QUERY REWRITE AS
3 SELECT T1.ID ID1, T2.ID ID2
4 FROM T1, T2
5 WHERE T1.ID = T2.ID (+);
實體化檢視已建立。
SQL> SELECT T1.ID, T2.ID
2 FROM T1, T2
3 WHERE T1.ID = T2.ID (+);
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1
0
已選擇9行。
執行計劃
----------------------------------------------------------
Plan hash value: 183449623
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T12 | 9 | 234 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
13 recursive calls
0 db block gets
24 consistent gets
1 physical reads
0 redo size
723 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
對於普通外連線是可以進行查詢重寫的，檢查是否是SQL標準語法導致的問題：
SQL> SELECT T1.ID, T2.ID
2 FROM T1 LEFT OUTER JOIN T2
3 ON T1.ID = T2.ID;
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1
0
已選擇9行。
執行計劃
----------------------------------------------------------
Plan hash value: 183449623
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T12 | 9 | 234 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
10 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
723 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
和標準語法也沒有關係，檢查全外連線能否使用這個物化檢視進行查詢重寫：
SQL> SELECT T1.ID, T2.ID
2 FROM T1 FULL OUTER JOIN T2
3 ON T1.ID = T2.ID;
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9
10
1
0
已選擇11行。
執行計劃
----------------------------------------------------------
Plan hash value: 53297166
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 9 (12)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 9 | 234 | 9 (12)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 9 | 234 | 9 (12)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 9 | 117 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 9 | 117 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
7 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> SELECT /*+ REWRITE (MV_T12) */ T1.ID, T2.ID
2 FROM T1 FULL OUTER JOIN T2
3 ON T1.ID = T2.ID;
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9
10
1
0
已選擇11行。
執行計劃
----------------------------------------------------------
Plan hash value: 53297166
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 9 (12)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 9 | 234 | 9 (12)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 9 | 234 | 9 (12)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 9 | 117 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 9 | 117 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
7 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL> SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */ T1.ID, T2.ID
2 FROM T1 FULL OUTER JOIN T2
3 ON T1.ID = T2.ID;
ID ID
---------- ----------
2 2
3 3
4 4
5 5
6 6
7 7
8 8
1
0
10
9
已選擇11行。
執行計劃
----------------------------------------------------------
Plan hash value: 665903394
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 468 | 12 (9)| 00:00:01 |
| 1 | VIEW | | 18 | 468 | 12 (9)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS FULL| MV_T12 | 9 | 234 | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN ANTI | | 9 | 234 | 9 (12)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 9 | 117 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 9 | 117 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Note
-----
- dynamic sampling used for this statement (level=2)
統計資訊
----------------------------------------------------------
22 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
733 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
直接利用REWRITE提示，Oracle不會利用物化檢視進行重新，但是如果利用NO_NATIVE_FULL_OUTER_JOIN提示，那麼Oracle會利用建立的物化檢視進行查詢重寫。
看來問題和metalink文件Bug 7032300描述的現象基本一致，看來這個bug在11.2中仍然沒有完全解決。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-676503/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 連線查詢超全詳解MySql
- 連線查詢
- 3.DQL資料查詢語言（內連線，外連線，自連線）
- MYSQL學習筆記23： 多表查詢（自連線內連線+左右外連線）MySql筆記
- oracle 連線查詢Oracle
- SQL連線查詢SQL
- sql 連線查詢例項（left join）三表連線查詢SQL
- MySQL之連線查詢和子查詢MySql
- mysql查詢語句5：連線查詢MySql
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- Python連線es筆記二之查詢方式彙總Python筆記
- 1112跨表連線查詢
- MySQL學習-連線查詢MySql
- oracle連線查詢詳解Oracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）SQL原始碼
- 輪詢、長輪詢、短連線、長連線區別對比
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別
- 二分查詢 | 二分查詢的一種推薦寫法
- MySQL內連線查詢語句MySql
- Hibernate【查詢、連線池、逆向工程】
- PHP連線、查詢MySQL資料庫PHPMySql資料庫
- MySQL講義第27講——select 查詢之自連線查詢MySql
- MySql中的資料查詢語言（DQL）三：連線查詢MySql
- mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢MySql
- 【大資料】SparkSql 連線查詢中的謂詞下推處理 (二)大資料SparkSQL
- mysql連表查詢出現資料重複MySql
- 從查詢重寫角度理解elasticsearch的高亮原理Elasticsearch
- 無法連線java visualvm外掛中心JavaLVM
- 離線查詢與線上查詢
- 查詢——二分查詢
- 陣列的查詢（搜尋）：線性查詢和二分法查詢陣列
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- nodejs 連線 mysql 查詢事務處理NodeJSMySql
- mysql常用連線查詢join，left,right,crossMySqlROS
- 資料庫學習（四）連線查詢資料庫
- PostgreSQL優化案例之 反連線與外連線等價改寫SQL優化
- Mysql中常用函式 分組,連線查詢MySql函式