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/，如需轉載，請註明出處，否則將追究法律責任。