oracle rownum的理解

一、Rownum的描述：

        rownum是一个伪列，数据库中并不保存rownum的列值，它是oracle系统为返回的结果集顺序分配的行编号，rownum是随着结果集生成的，一旦生成，在同一个结果集中就不会变化了，rownum值是依次递加的（从1开始），没有1就永远不会有2。

        当某一行记录读入内存时，相应的ROWNUM才被动态地赋值。 

二、Rownum运行机制：

1 Oracle executes your query.

2 Oracle fetches the first row and calls it row number 1.

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.

 

三、Rownum的使用：

a）特殊结果的输出：

1.Top N结果输出：      

SQL> select t.terminal_id,t.time_in,ROWNUM from tf_r_terminal_arch t WHERE t.terminal_state='0'  AND ROWNUM <5;
 
TERMINAL_ID                    TIME_IN         ROWNUM
------------------------------ ----------- ----------
700000003840655                2011/8/17            1
700000003840660                2011/7/28            2
700000003840659                2011/7/28            3
700000003840656                2011/7/28            4

2.分页查询（目前比较高效的分页方式）：      

SQL> SELECT a.* FROM (select t.terminal_id,t.eparchy_code,ROWNUM rn from tf_r_terminal_arch t WHERE t.terminal_state='0'
  2   AND ROWNUM <=7) a WHERE a.rn>=3 ;
 
TERMINAL_ID                    EPARCHY_CODE         RN
------------------------------ ------------ ----------
700000003840659                0898                  3
700000003840656                0898                  4
700000003840653                0898                  5
700000003840661                0899                  6
700000003840658                0899                  7

3.分组子排序：      

SQL> select t.terminal_id,t.eparchy_code,ROWNUM from tf_r_terminal_arch t WHERE t.terminal_state='0';
 
TERMINAL_ID                    EPARCHY_CODE     ROWNUM
------------------------------ ------------ ----------
700000003840655                0897                  1
700000003840660                0897                  2
700000003840659                0898                  3
700000003840656                0898                  4
700000003840653                0898                  5
700000003840661                0899                  6
700000003840658                0899                  7

比如对上面结果集，根据EAPRCHY_CODE分组，再进行小组内排序：

SQL> SELECT b.terminal_id, decode(ROWNUM - min_rn,0,c.eparchy_code,NULL) eparchy_code, ROWNUM - min_rn + 1 NO
  2  FROM (select t.terminal_id,t.eparchy_code from tf_r_terminal_arch t WHERE t.terminal_state='0') b,
  3  (SELECT eparchy_code, MIN(rn) min_rn FROM
  4       (select t.terminal_id,t.eparchy_code,ROWNUM rn from tf_r_terminal_arch t WHERE t.terminal_state='0') a
  5      GROUP BY a.eparchy_code) c WHERE b.eparchy_code = c.eparchy_code
  6  ;
 
TERMINAL_ID                    EPARCHY_CODE         NO
------------------------------ ------------ ----------
700000003840655                0897                  1
700000003840660                                      2
700000003840659                0898                  1
700000003840656                                      2
700000003840653                                      3
700000003840661                0899                  1
700000003840658                                      2

四、使用陷阱：

（1）排序的陷阱（针对排序字段非主键的情况）：

SQL> select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0';
 
TERMINAL_ID                    PURCHASE_PRICE
------------------------------ --------------
700000003840655                          1980
700000003840660                          1380
700000003840659                          1680
700000003840656                          1480
700000003840653                          3300
700000003840661                          2380
700000003840658                          1590

对上面结果集排序取PURCHASE_PRICE最低的3条记录，如果这样写sql会得出如下结果：

SQL> select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0' AND ROWNUM<=3
  2  ORDER BY t.purchase_price
  3  ;
 
TERMINAL_ID                    PURCHASE_PRICE
------------------------------ --------------
700000003840660                          1380
700000003840659                          1680
700000003840655                          1980

因为sql处理时rownum是先赋值的，所以会先取出前3条数据，然后再进行排序，而我们要的是排序后的价格最低的3条数据，应该这样：

SQL> SELECT * FROM (
  2  select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0'
  3  ORDER BY t.purchase_price ) a WHERE ROWNUM <=3
  4  ;
 
TERMINAL_ID                    PURCHASE_PRICE
------------------------------ --------------
700000003840660                          1380
700000003840656                          1480
700000003840658                          1590

（2）排序的陷阱（针对排序字段为主键的情况）： 

          对于排序字段为主键这种情况，也要分两种情况 ：（下面例子测试根据主键terminal_id进行排序：表tf_r_terminal_arch，主键terminal_id，terminal_state上建有索引IDX_TF_R_MOBILEDEVICE_STATE）

         1.where 条件中有索引字段条件：这种情况下会先走该字段进行索引查询，rownum赋值，最后根据主键排序：

SQL> select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0' AND  ROWNUM <=3
  2  ORDER BY t.terminal_id;
 
TERMINAL_ID                    PURCHASE_PRICE
------------------------------ --------------
700000003840655                          1980
700000003840659                          1680
700000003840660                          1380
 
SQL> explain plan for select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0' AND  ROWNUM <=3
  2  ORDER BY t.terminal_id;
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3199904717
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | By
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     3 |
|   1 |  SORT ORDER BY                |                             |     3 |
|*  2 |   COUNT STOPKEY               |                             |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| TF_R_TERMINAL_ARCH          |     7 |
|*  4 |     INDEX RANGE SCAN          | IDX_TF_R_MOBILEDEVICE_STATE |     7 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=3)
   4 - access("T"."TERMINAL_STATE"='0')
 
17 rows selected

从上面执行计划可以看出查询是先提取Top N进行rownum赋值（COUNT STOPKEY），后进行主键排序（SORT ORDER BY），其实这种情况和非主键排序是一样的，应该这样处理：

SQL> SELECT * FROM (
  2  select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_state='0'
  3  ORDER BY t.terminal_id ) a WHERE ROWNUM <=3;
 
TERMINAL_ID                    PURCHASE_PRICE
------------------------------ --------------
700000003840653                          3300
700000003840655                          1980
700000003840656                          1480
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2297731398
--------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | B
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     3 |
|*  1 |  COUNT STOPKEY                 |                             |       |
|   2 |   VIEW                         |                             |     7 |
|*  3 |    SORT ORDER BY STOPKEY       |                             |     7 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TF_R_TERMINAL_ARCH          |     7 |
|*  5 |      INDEX RANGE SCAN          | IDX_TF_R_MOBILEDEVICE_STATE |     7 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=3)
   3 - filter(ROWNUM<=3)
   5 - access("T"."TERMINAL_STATE"='0')
 
19 rows selected

上面就是先进行SORT ORDER BY STOPKEY （这一步就可以得到根据主键terminal_id排序的7条记录集），然后转化成view视图，COUNT STOPKEY根据rownum取出前3条记录。

         2.where条件中无索引字段条件：这种情况下会根据主键索引进行查询，先排序，后rownum赋值。 

SQL> explain plan for
  2  
  2  select t.terminal_id, t.purchase_price from tf_r_terminal_arch t WHERE t.terminal_type_code='01' AND  ROWNUM <=3
  3  ORDER BY t.terminal_id
  4  ;
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1588539088
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     3 |    63 |
|*  1 |  COUNT STOPKEY               |                       |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TF_R_TERMINAL_ARCH    |  2500 | 52500 |
|   3 |    INDEX FULL SCAN           | PK_TF_R_TERMINAL_ARCH |    13 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=3)
   2 - filter("T"."TERMINAL_TYPE_CODE"='01')
 
16 rows selected

 

说明：
执行计划中的COUNT STOPKEY 机制是只针对rownum而设定的，专门是为了提取top n 的需求优化的

SORT ORDER BY STOPKEY操作是Oracle处理rownum+orderby的一种优化算法。这个操作本质上很像算法中的快排序。首先是将数据集合分成两个部分（A和B），保证A中排序值都不小于B中排序值。在对A集合分割成两部分，依次进行。数据集B和其他那些小数据集合就不进行排序了。这样操作，只进行较少的排序次数，就可以最快的获取全局最大（或者最小）的几个数据。

 

以上是自己的一个理解情况，可能有不对的地方，希望大家一起指点讨论。