11GR2中RBO執行全外連線結果錯誤
意外發現11.2中，如果在RBO下執行全外連線，會得到錯誤的結果。
正常情況下：
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SET AUTOT ON
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)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
15 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
不管最佳化器是CHOOSE還是RULE，只要是RBO模式，無論是執行計劃還是最終查詢結果都是錯誤的：
SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;
會話已更改。
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
已選擇7行。
執行計劃
----------------------------------------------------------
Plan hash value: 3374424389
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | VW_FOJ_0 |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL| T2 |
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| T1 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
700 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
7 rows processed
SQL> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;
會話已更改。
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
已選擇7行。
執行計劃
----------------------------------------------------------
Plan hash value: 3374424389
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | VW_FOJ_0 |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL| T2 |
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| T1 |
-----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
700 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
7 rows processed
而這個錯誤在10g中不會出現：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30));
Table created.
SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));
Table created.
SQL> INSERT INTO T1
2 SELECT ROWNUM - 1, OBJECT_NAME
3 FROM USER_OBJECTS
4 WHERE ROWNUM < 10;
9 rows created.
SQL> INSERT INTO T2
2 SELECT ROWNUM + 1, OBJECT_NAME
3 FROM USER_OBJECTS
4 WHERE ROWNUM < 10;
9 rows created.
SQL> SET AUTOT ON
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
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
----------------------------------------------------------
15 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
718 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
SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;
Session altered.
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
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
718 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
SQL> SELECT /*+ RULE */ 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 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
718 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
雖然10g中Oracle根本不會選擇在RBO下執行全外連線，不過起碼保證查詢結果的正確性，不知道為什麼10g中不存在的錯誤在11g的RBO反倒出現了，理論上講11g的RBO應該沒有再做什麼修改才對。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-676142/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 11gr2全外連線優化執行計劃優化
- 11gr2全外連線最佳化執行計劃
- 11gr2全外連線優化執行計劃（二）優化
- 11gr2全外連線優化執行計劃（三）優化
- 11gr2全外連線最佳化執行計劃（三）
- 11gr2全外連線最佳化執行計劃（二）
- 11gR2 syaasm連線錯誤ASM
- Oracle內連線、外連線、右外連線、全外連線小總結Oracle
- Oracle 左外連線、右外連線、全外連線小總結Oracle
- 評“MySQL 隱式轉換引起的執行結果錯誤”MySql
- 【SQL】Oracle的內連線、左外連線、右外連線及全外連線SQLOracle
- 執行結果
- SQL的四種連線:內連線 左外連線 右外連線 全連線SQL
- 11GR2執行DBCA報錯誤(打補丁)
- 例項解析外連線 內連線 自連線 全連線
- 【執行計劃】Oracle 11gR2使用Full outer Joins執行計劃完成全外連線查詢Oracle
- Oracle 11gR2 RAC連線報錯ora-12537錯誤Oracle
- MYSQL語法：左連線、右連線、內連線、全外連線MySql
- 深入理解SQL的四種連線-左外連線、右外連線、內連線、全連線SQL
- 內連線、外連線總結
- springboot整合Batis，執行報錯，資料庫連線密碼錯誤Spring BootBAT資料庫密碼
- hadoop配置、執行錯誤總結Hadoop
- 寬頻連線錯誤678 寬頻連線錯誤691錯誤的解決辦法
- sql內連結，外連線SQL
- perl連線Oracle錯誤Oracle
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- hadoop配置、執行錯誤總結一Hadoop
- 在Linux中執行"rm -rf /"的結果Linux
- 內聯，左外聯，右外聯，全連線，交叉連線 的區別
- 執行錯誤集
- mysql 外連線總結MySql
- oracle sql內連線_左(右)連線_全外連線_幾種寫法OracleSQL
- SQL改寫系列九：外連線轉內連線的常見場景與錯誤SQL
- 執行指令碼寫入中間表錯誤返回錯誤資訊指令碼
- Linux中crontab定時任務未執行錯誤總結Linux
- Oracle左右全連線總結Oracle
- oracle 客戶端連線11gR2 SCAN 報ORA-12545錯誤Oracle客戶端
- SQL改寫系列九：外連線轉內連線的常見場景與錯誤-2SQL