Oracle中RowNum的用法

ROWNUM，是一种伪列，它根据特定记录返回一个序列化的数字。同伪列ROWID不同：ROWID是一种数据类型，唯一标识一条记录在物理位置的一个ID,基于64位编码的16个字符显示。
下面来介绍一下关于ROWNUM的使用技巧和注意事项。
一 特殊结果的输出：
1.1 TOP N的输出：

SELECT CM.POLICY_ID,CM.POLICY_CODE
          FROM T_CONTRACT_MASTER CM WHERE ROWNUM <= 5
          ORDER BY CM.POLICY_ID DESC;

1.2 分页处理,以下介绍三种方式：
1) 用BETWEEN...AND...实现:

SELECT TCM.POLICY_ID, TCM.POLICY_CODE
    FROM (SELECT CM.POLICY_ID,CM.POLICY_CODE,ROWNUM AS RN
          FROM T_CONTRACT_MASTER CM
          ORDER BY CM.POLICY_ID)TCM
    WHERE TCM.RN BETWEEN 5 AND 10;

2)MINUS方式:

SELECT CM.POLICY_ID,CM.POLICY_CODE
   FROM T_CONTRACT_MASTER CM
   WHERE ROWNUM <= 10
   MINUS
   SELECT TCM.POLICY_ID,TCM.POLICY_CODE
   FROM T_CONTRACT_MASTER TCM
   WHERE ROWNUM <= 5;

3)

SELECT *
   FROM (SELECT TCM.POLICY_ID,TCM.POLICY_CODE
         FROM (
               SELECT CM.POLICY_ID, CM.POLICY_CODE,ROWNUM AS RN
               FROM T_CONTRACT_MASTER CM
               WHERE ROWNUM<=10)
              ) TCM
         WHERE RN<=5
         )T;

1.3 利用ROWNUM进行分组子查询
对于以下表T_TEST4的内容：
OWNER NAME
------------------------------------------------------
STRMADMIN STREAMS_QUEUE
APARKMAN JOB_QUEUE
SYS AQ$_AQ_SRVNTFN_TABLE_E
SYS AQ$_KUPC$DATAPUMP_QUETAB_E
APARKMAN AQ$_JMS_TEXT_E
STRMADMIN AQ$_STREAMS_QUEUE_TABLE_E
SYS AQ$_SCHEDULER$_EVENT_QTAB_E
…
如果我们希望结果按照OWNER进行分组后，再对每组中成员进行编号，结果类似如下：
OWNER NO NAME
------------------------------------------------------
APARKMAN 1 JOB_QUEUE
2 AQ$_JMS_TEXT_E
STRMADMIN 1 STREAMS_QUEUE
2 AQ$_STREAMS_QUEUE_TABLE_E
SYS 1 AQ$_AQ_SRVNTFN_TABLE_E
2 AQ$_KUPC$DATAPUMP_QUETAB_E
3 AQ$_SCHEDULER$_EVENT_QTAB_E
…
在没有ROWNUM时要实现这样的功能会很复杂，但通过ROWNUM我们可以轻松实现：
SQL> SELECT DECODE(ROWNUM-min_sno,0,a.owner,NULL) owner,DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno, a.name
2 FROM (SELECT *
3 FROM t_test8
4 ORDER BY owner, name ) a,
5 (SELECT owner, MIN(rownum) min_sno
6 FROM( SELECT *
7 FROM t_test8
8 ORDER BY owner, name)
9 GROUP BY owner) b
10 WHERE a.owner=b.owner;
OWNER SNO NAME
------------------------------ ---------- ------------------------------
APARKMAN 1 JOB_QUEUE
2 AQ$_JMS_TEXT_E
STRMADMIN 1 STREAMS_QUEUE
2 AQ$_STREAMS_QUEUE_TABLE_E
SYS 1 AQ$_AQ_SRVNTFN_TABLE_E
2 AQ$_KUPC$DATAPUMP_QUETAB_E
3 AQ$_SCHEDULER$_EVENT_QTAB_E
4 AQ$_SCHEDULER$_JOBQTAB_E
5 AQ$_STREAMS_QUEUE_TABLE_E
6 AQ$_SYS$SERVICE_METRICS_TAB_E
7 AQ$_AQ_EVENT_TABLE_E
8 AQ$_AQ$_MEM_MC_E
9 AQ$_ALERT_QT_E
10 ALERT_QUE
11 AQ_EVENT_TABLE_Q
12 SYS$SERVICE_METRICS
13 STREAMS_QUEUE
14 SRVQUEUE
15 SCHEDULER$_JOBQ
16 SCHEDULER$_EVENT_QUEUE
17 AQ_SRVNTFN_TABLE_Q
SYSMAN 1 AQ$_MGMT_NOTIFY_QTABLE_E
2 MGMT_NOTIFY_Q
SYSTEM 1 DEF$_AQERROR
2 DEF$_AQCALL
3 AQ$_DEF$_AQERROR_E
4 AQ$_DEF$_AQCALL_E
WMSYS 1 AQ$_WM$EVENT_QUEUE_TABLE_E
2 WM$EVENT_QUEUE
29 rows selected.
From:http://blog.chinaunix.net/u1/38200/showart_2071748.html
2 性能:
一般来说，经常在编程中使用ROWNUM=1作为条件来查询数据。认为只要有数据，就能直接返回了。在ORACLE 10G以前的版本，要使用ROWNUM<2.

SELECT *
   FROM T_CONTRACT_MASTER 
   WHERE ROWNUM =1;

3.注意事项：
1) 不宜直接对ROWNUM使用">=",">"之类的操作：

SELECT *
    FROM T_CONTRACT_MASTER CM
    WHERE ROWNUM >1;

2)ROWNUM和ORDER BY的联合使用
只有当ORDER BY 的字段为PK键时，查询结果才会先排序再计算ROWNUM;

SELECT CM.POLICY_ID,CM.POLICY_CODE
   FROM T_CONTRACT_MASTER CM
   WHERE ROWNUM <5
   ORDER BY CM.POLICY_ID;

   SELECT CM.POLICY_ID,CM.POLICY_CODE
   FROM T_CONTRACT_MASTER CM
   WHERE ROWNUM <5
   ORDER BY CM.POLICY_CODE;

3)排序分页:
其实，排序分页与优化器有关联。