Oracle using ROWNUM will cause the query result set to solidify

Today, a friend of mine to see if a phenomenon is not a bug, if the direct query error, if nested one layer plus rownum will not be an error, if removed rownum will lead to false recurrence, regardless of the rownum specified query how many lines, will avoid the wrong generation.

First look at this error:

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

*

Line 7th error:

ORA-01722: Invalid number

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

*

Line 9th error:

ORA-01722: Invalid number

Sql> SELECT COUNT (*) from T_bid_item;

COUNT (*)

----------

26824

Sql> SELECT COUNT (*) from t_bid;

COUNT (*)

----------

4131

As described at the beginning of the article, the error may disappear after changing the way the query is written. With nested subqueries, and using RowNum, the error no longer appears, and if RowNum is removed, the error occurs. Whether the error occurs is related only to the inclusion of rownum, regardless of the number of records specified by RowNum. As you can see, two tables have a small number of records, even if the Cartesian product does not exceed the rownum specified 10000000000.

This column more highlights: http://www.bianceng.cn/database/Oracle/

In fact, the reason for this is very simple, rownum has a fixed result set function, the use of rownum in a subquery, will cause the result set to be cured, somewhat similar to the Oracle No_merge plus no_push_pred prompts the function. Check out these two SQL execution plans at a glance:

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 ')

WHERE bid_price > 1;

has been explained.

Sql> SELECT * from TABLE (Dbms_xplan. DISPLAY);

Plan_table_output

----------------------------------------------------------------------------------------

Plan Hash value:1595260930

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |

----------------------------------------------------------------------------------------