FIRST_ROWS優化模式訪問遠端表可能導致錯誤結果（一）
在Oracle的9204上碰到了這個問題，FIRST_ROWS優化模式下，通過資料庫鏈訪問遠端表，且SQL包括IN子查詢語句時，優化器可能給出錯誤的執行計劃，從而導致結果集出現重複記錄。
下面通過一個例子來重現這個問題：
SQL> CONN YANGTK/YANGTK@TEST1
已連線。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND OBJECT_TYPE IN ('TABLE', 'INDEX', 'SYNONYM');
表已建立。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_TAB_PRIVS;
表已建立。
SQL> CREATE INDEX IND_T1_GRANTEE ON T1(GRANTEE);
索引已建立。
SQL> CREATE INDEX IND_T1_NAME ON T1(TABLE_NAME);
索引已建立。
SQL> CREATE UNIQUE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已建立。
SQL> CONN YANGTK/YANGTK@YTK92
已連線。
SQL> DROP DATABASE LINK TEST1;
資料庫連結已刪除。
SQL> CREATE DATABASE LINK TEST1 USING 'TEST1';
資料庫連結已建立。
SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SYNONYMS;
表已建立。
SQL> CREATE INDEX IND_T2_NAME ON T2(SYNONYM_NAME);
索引已建立。
SQL> SET AUTOT ON EXP
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM T@TEST1
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3200 PSTUBTBL
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 FILTER
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 REMOTE* TEST1.YTK_THINKPAD
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 REMOTE* TEST1.YTK_THINKPAD
8 1 TABLE ACCESS (FULL) OF 'T2'
4 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
7 SERIAL_FROM_REMOTE SELECT /*+ */ DISTINCT "A1"."TABLE_NAME" FRO
M "T1" "A1" WHERE EXISTS (SELECT 0
而如果將優化模式改為FIRST_ROWS：
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3200 PSTUBTBL
3200 PSTUBTBL
3387 ODCI_SECOBJ$
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3388 ODCI_WARNINGS$
已選擇6行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1647 Card=
45 Bytes=4320)
1 0 FILTER
2 1 NESTED LOOPS (Cost=477 Card=45 Bytes=4320)
3 2 VIEW OF 'VW_NSO_1' (Cost=12 Card=462 Bytes=7854)
4 3 REMOTE* TEST1.YTK_THINKPAD
5 2 REMOTE* (Cost=1 Card=1 Bytes=79) TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
4 SERIAL_FROM_REMOTE SELECT "A1"."TABLE_NAME" FROM "T1" "A1" WHER
E EXISTS (SELECT 0 FROM "T1" "A2" W
5 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1" WHERE "OBJECT_NAME"=:1
現在得到的每條記錄都是重複的。根據上面的SQL和建表語句可以推斷，OBJECT_NAME是不會發生重複的，因此可以確定，在FIRST_ROWS模式下，Oracle產生了重複記錄。
其實詳細觀察Oracle的執行計劃也可以看到，由於採用了IN的方式，這裡的執行計劃應該是半連線，也就是SEMI-JOIN，但是Oracle卻錯誤的選擇了NESTED_LOOP執行計劃，下面看看ALL_ROWS和FIRST_ROWS(1)優化模式下的執行計劃：
SQL> SELECT /*+ FIRST_ROWS(1) */ OBJECT_ID, OBJECT_NAME FROM T@TEST1
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3200 PSTUBTBL
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=16 Card=1
Bytes=96)
1 0 FILTER
2 1 NESTED LOOPS (SEMI) (Cost=14 Card=1 Bytes=96)
3 2 REMOTE* (Cost=2 Card=31 Bytes=2449) TEST1.YTK_THINKPAD
4 2 VIEW OF 'VW_NSO_1' (Cost=12 Card=462 Bytes=7854)
5 4 REMOTE* TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=2
Card=1 Bytes=17)
3 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
5 SERIAL_FROM_REMOTE SELECT "A1"."TABLE_NAME" FROM "T1" "A1" WHER
E EXISTS (SELECT 0 FROM "T1" "A2" W
SQL> SELECT /*+ ALL_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@TEST1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3200 PSTUBTBL
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: ALL_ROWS (Cost=873 Card=33
Bytes=3168)
1 0 FILTER
2 1 HASH JOIN (SEMI) (Cost=15 Card=33 Bytes=3168)
3 2 REMOTE* (Cost=2 Card=33 Bytes=2607) TEST1.YTK_THINKPAD
4 2 VIEW OF 'VW_NSO_1' (Cost=12 Card=462 Bytes=7854)
5 4 REMOTE* TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
3 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_1"
5 SERIAL_FROM_REMOTE SELECT "A1"."TABLE_NAME" FROM "T1" "A1" WHER
E EXISTS (SELECT 0 FROM "T1" "A2" W
ALL_ROWS模式選擇了HASH_SJ，FIRST_ROWS(1)選擇了NL_SJ，因此都得到了正確的結果，而FIRST_ROWS由於使用了NEST_LOOP而導致結果集重複。
把SQL語句中的IN換成EXISTS語句，則不會出現錯誤的結果：
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1
2 WHERE EXISTS
3 (
4 SELECT 1 FROM T1@TEST1
5 WHERE TABLE_NAME = OBJECT_NAME
6 AND GRANTEE IN
7 (SELECT GRANTEE FROM T1@TEST1 WHERE TABLE_NAME = 'HELP')
8 )
9 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
3200 PSTUBTBL
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=78 Card=2
Bytes=158)
1 0 FILTER
2 1 REMOTE* (Cost=2 Card=2 Bytes=158) TEST1.YTK_THINKPAD
3 1 FILTER
4 3 REMOTE* (Cost=2 Card=5 Bytes=170) TEST1.YTK_THINKPAD
5 3 REMOTE* (Cost=2 Card=1 Bytes=34) TEST1.YTK_THINKPAD
6 1 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26
Card=511 Bytes=8687)
2 SERIAL_FROM_REMOTE SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "S
YS_ALIAS_2"
4 SERIAL_FROM_REMOTE SELECT "GRANTEE","TABLE_NAME" FROM "T1" "A1"
WHERE "TABLE_NAME"=:1
5 SERIAL_FROM_REMOTE SELECT "GRANTEE","TABLE_NAME" FROM "T1" "A2"
WHERE "GRANTEE"=:1 AND "TABLE_NAME"
如果將T和T1換成本地表，也不會出現錯誤：
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND OBJECT_TYPE IN ('TABLE', 'INDEX', 'SYNONYM');
表已建立。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_TAB_PRIVS;
表已建立。
SQL> CREATE INDEX IND_T1_GRANTEE ON T1(GRANTEE);
索引已建立。
SQL> CREATE INDEX IND_T1_NAME ON T1(TABLE_NAME);
索引已建立。
SQL> CREATE UNIQUE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已建立。
SQL> COL OBJECT_NAME FORMAT A30
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
29559 AW$AWCREATE
29567 AW$AWMD
29527 AW$EXPRESS
3387 ODCI_SECOBJ$
3388 ODCI_WARNINGS$
29573 OLAPTABLEVELS
29576 OLAPTABLEVELTUPLES
3200 PSTUBTBL
已選擇8行。
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=343 Card=6 Bytes=576)
1 0 NESTED LOOPS (Cost=343 Card=6 Bytes=576)
2 1 VIEW OF 'VW_NSO_1' (Cost=228 Card=113 Bytes=1921)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=34)
5 4 NESTED LOOPS (Cost=228 Card=113 Bytes=7684)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=113 Bytes=3842)
7 6 INDEX (RANGE SCAN) OF 'IND_T1_NAME' (NON-UNIQUE) (Cost=1 Card=45)
8 5 INDEX (RANGE SCAN) OF 'IND_T1_GRANTEE' (NON-UNIQUE) (Cost=1 Card=1)
9 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=79)
10 9 INDEX (UNIQUE SCAN) OF 'IND_T_NAME' (UNIQUE)
11 10 INDEX (FULL SCAN) OF 'IND_T2_NAME' (NON-UNIQUE) (Cost=26 Card=511 Bytes=8687)
由於本地和遠端表中的資料不同，這裡顯示的結果也不同，不過這個結果並沒有重複資料。雖然Oracle使用的也是NESTED_LOOP，但是Oracle在連線後使用了一個SORT UNIQUE的操作，這個操作可以去掉重複的記錄，而如果訪問的是遠端物件，則不存在這個操作。
從例子中幾點分析可以得到這篇文章最初的推論。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-249072/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- FIRST_ROWS優化模式訪問遠端表可能導致錯誤結果（二）優化模式
- 解決防火牆限制遠端連線MySQL（導致錯誤10060可能之一）防火牆MySql
- CHAR型別函式索引導致結果錯誤型別函式索引
- 錯誤資料導致優化器不識別（高階優化手法用盡，結果盡然是這樣）優化
- DB2訪問遠端資料庫通訊錯誤DB2資料庫
- MySQL:MySQL客戶端快取結果導致OOMMySql客戶端快取OOM
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 階梯訪問表優化優化
- Redis遠端訪問Redis
- mysql 遠端連結錯誤 ERROR 1130MySqlError
- 如何解決url傳參導致錯誤問題
- 錯誤思維導向致IT專案問題多
- TeamViewer遠端訪問View
- Linux sudo 漏洞可能導致未經授權的特權訪問Linux
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- 【優化】ALL_ROWS模式和FIRST_ROWS模式的適用場景優化模式
- 多餘索引導致explain錯誤索引AI
- 移動端API介面優化的術和結果API優化
- rabbitmq 新增遠端訪問功能MQ
- 遠端訪問及控制——ssh
- Linux SSH遠端訪問Linux
- PostgreSQL 9.6 配置遠端訪問SQL
- 繫結變數，組合查詢方式，導致CBO錯誤一例變數
- 一次錯誤使用 go-cache 導致出現的線上問題Go
- 核心表AUTOTRACE結果出錯
- iptables導致無法遠端連線oracleOracle
- linux遠端訪問問題薦Linux
- 用SecureCRTPortable連結遠端伺服器用sz下載檔案錯誤問題Securecrt伺服器
- GoldenGate MSSQL Oracle的主鍵問題導致的錯誤GoSQLOracle
- OGG 表結構變化導致同步異常
- impdp時parallel=4導致的錯誤Parallel
- 遠端客戶端 訪問 ASM 例項客戶端ASM
- mysql如何允許遠端訪問MySql
- jupyter notebook 遠端訪問
- mysql賬戶新增遠端訪問MySql
- NAS如何實現遠端訪問
- 遠端訪問jupyter notebook
- django：runserver實現遠端訪問DjangoServer