在Oracle的9204上碰到了这个问题，FIRST_ROWS优化模式下，通过数据库链访问远端表，且SQL包括IN子查询语句时，优化器可能给出错误的执行计划，从而导致结果集出现重复记录。
这一篇继续研究问题和版本的关系，以及相应的解决方法。
FIRST_ROWS优化模式访问远端表可能导致错误结果（一）：http://yangtingkun.itpub.net/post/468/460444
上一篇讨论了问题和哪些因素有关，下面看看这个问题是否和版本有关。由于上面一篇文章中所有的测试都是在9.2.0.4版本上进行的，下面分别将远端库和本地库更改为10.2，检查错误是否依然存在：
SQL> CONN YANGTK/YANGTK@YTK102
已连接。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND OBJECT_TYPE IN ('TABLE', 'SEQUENCE', '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> CREATE DATABASE LINK YTK102 USING 'YTK102';
数据库链接已创建。
SQL> SET AUTOT ON EXP
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM T@YTK102
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK102
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK102 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
46535 AW$AWCREATE
46541 AW$AWCREATE10G
46529 AW$AWMD
46553 AW$AWREPORT
46547 AW$AWXML
46523 AW$EXPRESS
7103 IMPDP_STATS
7102 KU$NOEXP_TAB
4312 ODCI_SECOBJ$
4313 ODCI_WARNINGS$
46619 OLAPI_HISTORY
46618 OLAPI_HISTORY_SEQ
46623 OLAPI_IFACE_OBJECT_HISTORY
46625 OLAPI_IFACE_OP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
46627 OLAPI_MEMORY_OP_HISTORY
46621 OLAPI_SESSION_HISTORY
46560 OLAPTABLEVELS
46563 OLAPTABLEVELTUPLES
48705 OLAP_OLEDB_FUNCTIONS_PVT
48668 OLAP_OLEDB_KEYWORDS
48671 OLAP_OLEDB_MDPROPS
48669 OLAP_OLEDB_MDPROPVALS
8706 PLAN_TABLE$
3989 PSTUBTBL
5287 SCHEDULER$_JOBSUFFIX_S
4161 WRI$_ADV_ASA_RECO_DATA
已选择27行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 FILTER
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 REMOTE* YTK102.YTK_THINKPAD
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 REMOTE* YTK102.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
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@YTK102
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK102
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK102 WHERE TABLE_NAME = 'HELP')
7 )
8 AND OBJECT_NAME NOT IN (SELECT SYNONYM_NAME FROM T2);
OBJECT_ID OBJECT_NAME
---------- ------------------------------
3989 PSTUBTBL
3989 PSTUBTBL
4161 WRI$_ADV_ASA_RECO_DATA
4161 WRI$_ADV_ASA_RECO_DATA
4161 WRI$_ADV_ASA_RECO_DATA
4312 ODCI_SECOBJ$
4312 ODCI_SECOBJ$
.
.
.
46629 OLAPI_MEMORY_HEAP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
46629 OLAPI_MEMORY_HEAP_HISTORY
48668 OLAP_OLEDB_KEYWORDS
48669 OLAP_OLEDB_MDPROPVALS
48671 OLAP_OLEDB_MDPROPS
48705 OLAP_OLEDB_FUNCTIONS_PVT
已选择105行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=2930 Card=
77 Bytes=7392)
1 0 FILTER
2 1 NESTED LOOPS (Cost=928 Card=77 Bytes=7392)
3 2 VIEW OF 'VW_NSO_1' (Cost=49 Card=874 Bytes=14858)
4 3 REMOTE* YTK102.YTK_THINKPAD
5 2 REMOTE* (Cost=1 Card=1 Bytes=79) YTK102.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
如果远端数据库是10g，那么问题仍然存在，其实这也可以理解，因为执行计划是本地产生的，所以远端数据库的版本并不重要。
下面将SQL在10g上执行，访问远端9I数据库：
SQL> CONN YANGTK/YANGTK@YTK102
已连接。
SQL> CREATE DATABASE LINK YTK92 USING 'YTK92';
数据库链接已创建。
SQL> SET AUTOT ON EXP
SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SYNONYMS;
表已创建。
SQL> CREATE INDEX IND_T2_NAME ON T2(SYNONYM_NAME);
索引已创建。
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM T@YTK92
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK92
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK92 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行。
执行计划
----------------------------------------------------------
Plan hash value: 4134023224
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 10848 | 79 (3)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | HASH JOIN | | 113 | 10848 | 23 (14)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 113 | 1921 | 19 (11)| 00:00:01 | | |
| 4 | REMOTE | | | | | | YTK92 | R->S |
| 5 | REMOTE | T | 899 | 71021 | 3 (0)| 00:00:01 | YTK92 | R->S |
|* 6 | INDEX RANGE SCAN| IND_T2_NAME | 210 | 3570 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE "SYNONYM_NAME"=:B1))
2 - access("OBJECT_NAME"="$nso_col_1")
6 - access("SYNONYM_NAME"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS1563' INTO PLAN_TABLE@! FOR SELECT DISTINCT
"A1"."TABLE_NAME" FROM "T1" "A2","T1" "A1" WHERE "A1"."GRANTEE"="A2"."GRANTEE" AND
"A2"."TABLE_NAME"='HELP' (accessing 'YTK92.YTK_THINKPAD' )
5 - SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "SYS_ALIAS_1" (accessing
'YTK92.YTK_THINKPAD' )
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@YTK92
2 WHERE OBJECT_NAME IN
3 (
4 SELECT TABLE_NAME FROM T1@YTK92
5 WHERE GRANTEE IN
6 (SELECT GRANTEE FROM T1@YTK92 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行。
执行计划
----------------------------------------------------------
Plan hash value: 4134023224
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113 | 10848 | 79 (3)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | HASH JOIN | | 113 | 10848 | 23 (14)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1 | 113 | 1921 | 19 (11)| 00:00:01 | | |
| 4 | REMOTE | | | | | | YTK92 | R->S |
| 5 | REMOTE | T | 899 | 71021 | 3 (0)| 00:00:01 | YTK92 | R->S |
|* 6 | INDEX RANGE SCAN| IND_T2_NAME | 210 | 3570 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T2" "T2" WHERE "SYNONYM_NAME"=:B1))
2 - access("OBJECT_NAME"="$nso_col_1")
6 - access("SYNONYM_NAME"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - EXPLAIN PLAN SET STATEMENT_ID='PLUS1563' INTO PLAN_TABLE@! FOR SELECT DISTINCT
"A1"."TABLE_NAME" FROM "T1" "A2","T1" "A1" WHERE "A1"."GRANTEE"="A2"."GRANTEE" AND
"A2"."TABLE_NAME"='HELP' (accessing 'YTK92.YTK_THINKPAD' )
5 - SELECT "OBJECT_NAME","OBJECT_ID" FROM "T" "SYS_ALIAS_1" (accessing
'YTK92.YTK_THINKPAD' )
Note
-----
- dynamic sampling used for this statement
在10g中，Oracle改变了策略，无论是ALL_ROWS还是FIRST_ROWS都使用了HASH JOIN作为连接方式，这种方式尤其适合通过数据库链的访问远端对象，从这里也可以看到Oracle 10g在处理远端对象方面所进行的优化。
这里没有使用SEMI-JOIN方式，但是也得到了正确的结果，这是由于Oracle在访问远端对象的时候在语句中添加了DISTINCT，从而去除了重复记录，由此可见，这个bug在10.2上已经被解决了。
顺便提一下METALINK的搜索结果，这次在METALINK上倒不是找不到相关的bug，而是找到了太多类似的情况，不过每种情况Oracle并没有进行详细的分析和说明，以至于根据现象无法断定目前的bug到底属于其中的哪一个，比较类似的bug包括：3092670、2469769、3273161和3041166等。没有办法根据metalink上提供的信息来确定解决方法，好在这些bug基本上都在10.1版本被fixed，基本上可以确定这个bug不会影响10g的版本。
至于解决问题的方法，其实上来在确定问题时已经相应的介绍了很多种了，只要破坏下面的任意一种情况，bug就不会出现：9I、FIRST_ROWS、DB_LINK访问远端对象，包含IN子查询语句。
除了这些方法外，添加HINT也是一种解决问题的方法，比如添加HASH_SJ，MERGE_SJ，NL_SJ等：
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT /*+ HASH_SJ */ 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: FIRST_ROWS (Cost=873 Card=3
3 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 /*+ HASH_SJ */ "A1"."TABLE_NAME" FROM
"T1" "A1" WHERE EXISTS (SELECT 0 F
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
2 WHERE OBJECT_NAME IN
3 (
4 SELECT /*+ NL_SJ */ 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=1256 Card=
33 Bytes=3168)
1 0 FILTER
2 1 NESTED LOOPS (SEMI) (Cost=398 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 /*+ NL_SJ */ "A1"."TABLE_NAME" FROM "
T1" "A1" WHERE EXISTS (SELECT 0 FRO
除此之外，添加DRIVING_SITE对于当前的bug也是一个不错的解决方法：
SQL> SELECT /*+ FIRST_ROWS DRIVING_SITE(T) */ OBJECT_ID, OBJECT_NAME FROM T@TEST1 T
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 (REMOTE) ptimizer=HINT: FIRST_ROWS (Cost=2
80 Card=5 Bytes=480)
1 0 NESTED LOOPS (Cost=280 Card=5 Bytes=480)
2 1 VIEW OF 'VW_NSO_1' (Cost=186 Card=92 Bytes=1564) TEST1.YANGTINGKUN
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 TEST1.YANGTINGKUN
Bytes=34)
5 4 NESTED LOOPS (Cost=186 Card=92 Bytes=6256)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Ca TEST1.YANGTINGKUN
rd=92 Bytes=3128)
7 6 INDEX (RANGE SCAN) OF 'IND_T1_NAME' (NON-UNIQU TEST1.YANGTINGKUN
E) (Cost=1 Card=37)
8 5 INDEX (RANGE SCAN) OF 'IND_T1_GRANTEE' (NON-UNIQ TEST1.YANGTINGKUN
UE) (Cost=1 Card=1)
9 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Byte TEST1.YANGTINGKUN
s=79)
10 9 INDEX (UNIQUE SCAN) OF 'IND_T_NAME' (UNIQUE) TEST1.YANGTINGKUN
11 10 REMOTE* (Cost=26 Card=511 Bytes=8687) !
11 SERIAL_FROM_REMOTE SELECT "SYNONYM_NAME" FROM "T2" "A2" WHERE L
NNVL("SYNONYM_NAME"<>:1)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-251883/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-251883/
892
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
