使用rownum改變執行計劃的一個典型情況

今天在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進行限制，否則效能上會相差很大。