今天在itpub上看到一个案例。提到一个查询，如果使用rownum进行约束的时候，执行结果正常，而如果不用rownum则会报“ora-01722：无效数字”这样的错误。
而实际rownum的限制并没有对结果集产生任何影响（rownum<=100000000，范围放的很宽）。看了yangtingkun的解释（http://space.itpub.net/4227/viewspace-662899），决定自己做个实验验证下。
首先创建表和测试数据：
SQL> drop table a purge;
Table dropped
SQL> drop table b purge;
Table dropped
SQL> create table a(i varchar2(10)) nologging;
Table created
SQL> create table b(i varchar2(10)) nologging;
Table created
SQL> truncate table a;
Table truncated
SQL> truncate table b;
Table truncated
SQL> insert into a
2 select rownum
3 from dual connect by rownum <= 100;
100 rows inserted
SQL> insert into b
2 select case when rownum = 87 then 'a' else to_char(rownum) end
3 from dual connect by rownum <= 100;
100 rows inserted
SQL> commit;
Commit complete
构造一个与问题SQL一样的sql语句：
SQL> select * from (
2 select a.i ai, b.i bi
3 from a, b
4 where a.i = b.i
5 and rownum <= 100000000) t
6 where t.bi > 1
7 /
AI BI
---------- ----------
2 2
3 3
4 4
....
99 99
100 100
98 rows selected
SQL> select * from (
2 select a.i ai, b.i bi
3 from a, b
4 where a.i = b.i
5 /*and rownum <= 100000000*/) t
6 where t.bi > 1
7 /
select * from (
select a.i ai, b.i bi
from a, b
where a.i = b.i
/*and rownum <= 100000000*/) t
where t.bi > 1
ORA-01722: 无效数字
现象被模拟出来了。观察其执行计划：
没有rownum的执行计划：
customer21@ORCL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 4090908061
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 31 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 56 | 31 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| B | 4 | 28 | 15 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 100 | 700 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."I"="B"."I")
2 - filter(TO_NUMBER("B"."I")>1)
Note
-----
- dynamic sampling used for this statement
已选择20行。
加了rownum的执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2246457312
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1400 | 31 (4)| 00:00:01 |
|* 1 | VIEW | | 100 | 1400 | 31 (4)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | HASH JOIN | | 100 | 1400 | 31 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 100 | 700 | 15 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 100 | 700 | 15 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("T"."BI")>1)
2 - filter(ROWNUM<=100000000)
3 - access("A"."I"="B"."I")
Note
-----
- dynamic sampling used for this statement
已选择23行。
已用时间: 00: 00: 00.06
可以看出(红色加粗部分)，未加rownum的执行计划首先对b表进行了全表扫描，检索大于1的值（肯定会遇到字符'a'），然后再两表关联。而加了rownum后，执行计划发生了改变，首先对两表进行了关联（即排除掉了字符'a'的情况），然后再扫描结果视图大于1的值，于是便跳过了对字符'a'进行to_number隐式转换会发生的异常，并且结果集也是正确的。
总结：以上问题，主要是由于加上rownum对执行计划的改变引起的。本质上来说，是执行的顺序发生了变化，使得原本先执行的步骤A在加入rownum后被排到后面，而后执行的步骤B被放到前面来后，将结果集进行了固定，使得传给步骤A的结果集不再含有非数字以外的字符，因此对中间结果集过滤中所作的to_number转换便不会发生异常而返回最终的结果集。
用dingjun123的话讲，如果不用rownum，则外层查询的条件全部会推进到内层查询，而用了rownum，则不管限制什么范围，首先执行内层查询，然后再做外层查询。
但是在另外一种情况下，是希望能够被改变执行计划的。
1、创建表emp：
create table emp as select * from scott.emp;
2、创建索引：
create index idx_tt on emp(empno,ename,deptno) nologging;
3、执行查询：
select *
from (select t.*
from emp t
where t.ename='MILLER'
and t.deptno=10)
where empno=7934;
以上查询，是为了查询empno为7394的员工。这样的查询，Oracle会将外层查询的条件推入到内层查询，使得内层查询可以走idx_tt索引（确保数据足够多）：
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 174 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."EMPNO"=7934 AND "T"."ENAME"='MILLER' AND "T"."DEPTNO"=10)
可以知道这时的执行计划是最优的。但是如果在内层查询中使用了rownum条件限制，则将改变执行计划：
lyon@LYON> select * from (select t.* from emp t where t.ename='MILLER' and t.deptno=10 and rownum=1) where empno=7934;
Execution Plan
----------------------------------------------------------
Plan hash value: 3476215704
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 87 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7934)
2 - filter(ROWNUM=1)
3 - filter("T"."ENAME"='MILLER' AND "T"."DEPTNO"=10)
从上面可以很清楚的发现，empno=7934被推到了外层（由于rownum的存在）。整个执行顺序也就是我们所看到的语句的顺序，即首先进行ename='miller'和deptno=10，然后限制行数rownum=1，再转到外层进行empno=7934过滤。
不仅是在where条件中限制rownum会导致计划的改变，在select子句中使用rownum同样也会改变计划：
lyon@LYON> select * from (select rownum rn,t.* from emp t where t.ename='MILLER' and t.deptno=10 ) where empno=7934;
Execution Plan
----------------------------------------------------------
Plan hash value: 2077119879
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 2 | 200 | 2 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 2 | 174 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7934)
3 - filter("T"."ENAME"='MILLER' AND "T"."DEPTNO"=10)
在这种情况下，如果ename和deptno过滤后的结果集仍然很大，那么最终对性能的影响也会很大。
最终从性能上来考虑，如果嵌套查询是以上的这种情况，我们不能在内层查询中使用rownum进行限制，否则性能上会相差很大。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12932950/viewspace-662933/ ，如需转载，请注明出处，否则将追究法律责任。
