使用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的时候被过滤掉，因此这个查询可以顺利执行。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-662899/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-662899/