使用ROWNUM將導致查詢結果集的固化

今天一個朋友找我看看一個現象是不是bug，如果直接查詢報錯，如果巢狀一層加上ROWNUM則不會報錯，如果去掉ROWNUM就會導致錯誤重現，不管ROWNUM中指定查詢多少行，都會避免錯誤的產生。

首先來看這個錯誤：

SQL> SELECT COUNT(*)
  2  FROM T_BID_ITEM A, T_BID B
  3  WHERE A.BID_ORGID = B.BID_ORGID
  4  AND A.INVITE_ID = '3003'
  5  AND B.INVITE_ID = '3003'
  6  AND B.ENCRYPT_FLAG = 'N'
  7  AND TO_NUMBER(BID_PRICE) > 1;
AND TO_NUMBER(BID_PRICE) > 1
    *
第 7 行出現錯誤:
ORA-01722: 無效數字

SQL> SELECT COUNT(*)
  2  FROM (SELECT A.BID_PRICE
  3  FROM T_BID_ITEM A, T_BID B
  4  WHERE A.BID_ORGID = B.BID_ORGID
  5  AND A.INVITE_ID = '3003'
  6  AND B.INVITE_ID = '3003'
  7  AND ROWNUM < 10000000000
  8  AND B.ENCRYPT_FLAG = 'N')
  9  WHERE BID_PRICE > 1;

  COUNT(*)
----------
     26559

SQL> SELECT COUNT(*)
  2  FROM (SELECT A.BID_PRICE
  3  FROM T_BID_ITEM A, T_BID B
  4  WHERE A.BID_ORGID = B.BID_ORGID
  5  AND A.INVITE_ID = '3003'
  6  AND B.INVITE_ID = '3003'
  7  --AND ROWNUM < 10000000000
  8  AND B.ENCRYPT_FLAG = 'N')
  9  WHERE BID_PRICE > 1;
WHERE BID_PRICE > 1
      *
第 9 行出現錯誤:
ORA-01722: 無效數字

SQL> SELECT COUNT(*) FROM T_BID_ITEM;

  COUNT(*)
----------
     26824

SQL> SELECT COUNT(*) FROM T_BID;

  COUNT(*)
----------
      4131

正如文章開頭描述的，改變了查詢的寫法後，錯誤有可能消失。使用巢狀子查詢的方式，並使用ROWNUM，錯誤就不再出現，而如果去掉ROWNUM，則錯誤出現。錯誤出現與否只與是否包含ROWNUM有關，而和ROWNUM指定的記錄數無關。可以看到，兩個表的記錄數都不大，即使笛卡兒積的結果也不會超過ROWNUM指定的10000000000。

其實產生這種情況的原因很簡單，ROWNUM有固定結果集的功能，在一個子查詢中使用ROWNUM，會導致結果集被固化，有點類似Oracle中NO_MERGE加NO_PUSH_PRED提示的功能。檢查這兩個SQL的執行計劃就一目瞭然了：

SQL> EXPLAIN PLAN FOR
  2  SELECT COUNT(*)
  3  FROM (SELECT A.BID_PRICE
  4  FROM T_BID_ITEM A, T_BID B
  5  WHERE A.BID_ORGID = B.BID_ORGID
  6  AND A.INVITE_ID = '3003'
  7  AND B.INVITE_ID = '3003'
  8  --AND ROWNUM < 10000000000
  9  AND B.ENCRYPT_FLAG = 'N')
 10  WHERE BID_PRICE > 1;

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1595260930

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |   322 |    19   (6)|
|   1 |  SORT AGGREGATE               |                   |     1 |   322 |            |
|*  2 |   HASH JOIN                   |                   |    30 |  9660 |    19   (6)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| T_BID_ITEM        |    28 |  8148 |     4   (0)|
|*  4 |     INDEX RANGE SCAN          | T_BID_ITEM_INVITE |   225 |       |     1   (0)|
|*  5 |    TABLE ACCESS FULL          | T_BID             |  1059 | 32829 |    14   (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."BID_ORGID"="B"."BID_ORGID")
   3 - filter(TO_NUMBER("A"."BID_PRICE")>1)
   4 - access("A"."INVITE_ID"=3003)
   5 - filter("B"."INVITE_ID"=3003 AND "B"."ENCRYPT_FLAG"='N')

已選擇20行。

為了顯示方便，將執行計劃中的時間部分去掉了。

可以看到，Oracle執行索引掃描後，在掃描T_BID_ITEM表的時候執行TO_NUMBER(BID_PRICE)報錯。

而如果加上ROWNUM則執行計劃變為：

SQL> EXPLAIN PLAN FOR
  2  SELECT COUNT(*)
  3  FROM (SELECT A.BID_PRICE
  4  FROM T_BID_ITEM A, T_BID B
  5  WHERE A.BID_ORGID = B.BID_ORGID
  6  AND A.INVITE_ID = '3003'
  7  AND B.INVITE_ID = '3003'
  8  AND ROWNUM < 10000000000
  9  AND B.ENCRYPT_FLAG = 'N')
 10  WHERE BID_PRICE > 1;

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 3192764333

-------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |   252 |   171   (3)|
|   1 |  SORT AGGREGATE       |            |     1 |   252 |            |
|*  2 |   VIEW                |            | 27484 |  6763K|   171   (3)|
|*  3 |    COUNT STOPKEY      |            |       |       |            |
|*  4 |     HASH JOIN         |            | 27484 |  8642K|   171   (3)|
|*  5 |      TABLE ACCESS FULL| T_BID      |  1059 | 32829 |    14   (0)|
|*  6 |      TABLE ACCESS FULL| T_BID_ITEM | 25946 |  7373K|   156   (2)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("BID_PRICE")>1)
   3 - filter(ROWNUM<10000000000)
   4 - access("A"."BID_ORGID"="B"."BID_ORGID")
   5 - filter("B"."INVITE_ID"=3003 AND "B"."ENCRYPT_FLAG"='N')
   6 - filter("A"."INVITE_ID"=3003)

Note
-----
   - dynamic sampling used for this statement

已選擇26行。

這時的限定條件TO_NUMBER(BIT_PRICE) > 1在第2步執行，這步發生在HASH JOIN操作完成之後。由於ROWNUM語句的存在使得子查詢結果集固化，外層的查詢條件TO_NUMBER(BIT_PRICE) > 1沒有被推到內層查詢中，使得導致錯誤產生的記錄在進行HASH JOIN的時候被過濾掉，因此這個查詢可以順利執行。