声明：
本文转载出自：http://www.blogjava.net/conans/articles/219693.html
ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。
1特殊结果输出
1.1Top N结果输出
我们如果希望取输出结果的前面几条数据，通过ROWNUM可以轻松实现：
sql>select*fromt_test4
2whererownum<=5;
USERNAMEUSER_IDCREATED
-------------------------------------------------
WOW7126-APR-07
CS27015-JAN-07
36901-NOV-06
DMP6812-OCT-06
PROFILER6705-SEP-06
但是，如果你希望对一个排序结果取Top N数据的话，使用ROWNUM存在一些“陷阱”，我们后面部分会介绍这些“陷阱”并且说明如何避免。
1.2分页查询
利用ROWNUM对结果进行分页，下面返回结果中的第6到第10条记录：
sql>select*from
2(
3selecta.*,rownumasrnfromcss_bl_viewa
4wherecapture_phone_num='(1)925-4604800'
5)b
6whereb.rnbetween6and10;
6rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2770Card=2183Bytes=7166789)
10VIEW(Cost=2770Card=2183Bytes=7166789)
21COUNT
32tableACCESS(FULL)OF'CSS_BL_VIEW'(Cost=2770Card=2183Bytes=1305434)
Statistics
0recursivecalls
0dbblockgets
29346consistentgets
29190physicalreads
0redosize
7328bytessentviasql*Nettoclient
234bytesreceivedviasql*Netfromclient
4sql*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
5rowsprocessed
另外一种实现方式：
sql>select*fromcss_bl_viewa
2wherecapture_phone_num='(1)925-4604800'
3andrownum<=10
4minus
5select*fromcss_bl_viewa
6wherecapture_phone_num='(1)925-4604800'
7andrownum<=5
8;
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5920Card=10Bytes=8970)
10MINUS
21SORT(UNIQUE)(Cost=2960Card=10Bytes=5980)
32COUNT(STOPKEY)
43TABLEACCESS(FULL)OF'CSS_BL_VIEW'(Cost=2770Card=2183Bytes=1305434)
51SORT(UNIQUE)(Cost=2960Card=5Bytes=2990)
65COUNT(STOPKEY)
76tableACCESS(FULL)OF'CSS_BL_VIEW'(Cost=2770Card=2183Bytes=1305434)
62consistentgets
50physicalreads
7232bytessentviasql*Nettoclient
2sorts(memory)
第三种实现方式：5andrownum<=10
6)b
7whereb.rn>5;
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2770Card=10Bytes=32830)
10VIEW(Cost=2770Card=10Bytes=32830)
21COUNT(STOPKEY)
35consistentgets
30physicalreads
7271bytessentviasql*Nettoclient
这里特地将三种实现方式的查询计划及统计数据打印出来，大家可以比较一下3中方式的性能。1.3利用ROWNUM做分组子排序
对于以下表T_TEST4的内容：
OWNERNAME
------------------------------------------------------
STRMADMINSTREAMS_QUEUE
APARKMANJOB_QUEUE
SYSAQ$_AQ_SRVNTFN_TABLE_E
SYSAQ$_KUPC$DATAPUMP_QUETAB_E
APARKMANAQ$_JMS_TEXT_E
STRMADMINAQ$_STREAMS_QUEUE_TABLE_E
SYSAQ$_SCHEDULER$_EVENT_QTAB_E
…
如果我们希望结果按照OWNER进行分组后，再对每组中成员进行编号，结果类似如下：
OWNERNONAME
APARKMAN1JOB_QUEUE
2AQ$_JMS_TEXT_E
STRMADMIN1STREAMS_QUEUE
2AQ$_STREAMS_QUEUE_TABLE_E
SYS1AQ$_AQ_SRVNTFN_TABLE_E
2AQ$_KUPC$DATAPUMP_QUETAB_E
3AQ$_SCHEDULER$_EVENT_QTAB_E
在没有ROWNUM时要实现这样的功能会很复杂，但通过ROWNUM我们可以轻松实现：sql>SELECTDECODE(ROWNUM-min_sno,a.owner,NULL)owner,DECODE(ROWNUM-min_sno,1,rownum+1-min_sno)sno,a.name
2FROM(SELECT*
3FROMt_test8
4ORDERBYowner,name)a,
5(SELECTowner,MIN(rownum)min_sno
6FROM(SELECT*
7FROMt_test8
8ORDERBYowner,name)
9GROUPBYowner)b
10WHEREa.owner=b.owner;
OWNERSNONAME
----------------------------------------------------------------------
4AQ$_SCHEDULER$_JOBQTAB_E
5AQ$_STREAMS_QUEUE_TABLE_E
6AQ$_SYS$SERVICE_METRICS_TAB_E
7AQ$_AQ_EVENT_TABLE_E
8AQ$_AQ$_MEM_MC_E
9AQ$_ALERT_QT_E
10ALERT_QUE
11AQ_EVENT_TABLE_Q
12SYS$SERVICE_METRICS
13STREAMS_QUEUE
14SRVQUEUE
15SCHEDULER$_JOBQ
16SCHEDULER$_EVENT_QUEUE
17AQ_SRVNTFN_TABLE_Q
SYSMAN1AQ$_MGMT_NOTIFY_QTABLE_E
2MGMT_NOTIFY_Q
system1DEF$_AQERROR
2DEF$_AQCALL
3AQ$_DEF$_AQERROR_E
4AQ$_DEF$_AQCALL_E
WMSYS1AQ$_WM$EVENT_QUEUE_TABLE_E
2WM$EVENT_QUEUE
29rowsselected.
2性能
我们很多程序员在确认某个表中是否有相应数据时，喜欢加上ROWNUM=1，其思路就是只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。但是在10G之前，使用ROWNUM=1是不能达到预期的性能效果的，而是需要通过<2或<=1作为过滤条件才能达到预期效果，看以下查询计划：
sql>select*fromt_test1
2whereobject_id<100
3andrownum=1;
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=37Card=1Bytes=86)
10COUNT(STOPKEY)
21tableACCESS(BYINDEXROWID)OF'T_TEST1'(Cost=37Card=89Bytes=7654)
32INDEX(RANGESCAN)OF'T_TEST1_PK'(UNIQUE)(Cost=2Card=89)
0physicalreads
654bytessentviasql*Nettoclient
1rowsprocessed
3andrownum<=1;
3consistentgets
sql>/
1rowsprocessed
10G以后，这个问题就被修正了：
2whererownum=1;
Planhashvalue:536364188
------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time|
|0|SELECTSTATEMENT||1|86|2(0)|00:00:01|
|*1|COUNTSTOPKEY||||||
|2|tableACCESSFULL|T_TEST1|1|86|2(0)|00:00:01|
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter(ROWNUM=1)
1recursivecalls
4consistentgets
1physicalreads
1201bytessentviasql*Nettoclient
385bytesreceivedviasql*Netfromclient
2sql*Netroundtripsto/fromclient
2whererownum<=1;
1-filter(ROWNUM<=1)
3ROWNUM的使用“陷阱”
由于ROWNUM是一个伪列，只有有结果记录时，ROWNUM才有相应数据，因此对它的使用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。
3.1对ROWNUM进行>、>=、=操作
不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
sql>selectcount(*)fromcss_bl_viewawhererownum>0;
COUNT(*)
----------
361928
sql>selectcount(*)fromcss_bl_viewa
2whererownum>1;
0
这是因为：
1、ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；
2、返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1;
这样，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。上述查询可以通过子查询来替代：
sql>selectcount(*)
2from
3(selectBL_REF_CDE,rownumrnfromcss_bl_view)
4wherern>1;
361927
我们可以通过以下方式来实现对ROWNUM的>、=的查询：
查询ROWNUM=5的数据：
sql>selectobject_id,object_name
2from(selectobject_id,object_name,rownumasrnfromt_test1)
3wherern=5;
OBJECT_IDOBJECT_NAME
----------------------------------------
29C_COBJ#
查询ROWNUM > 25的数据：
2minus
3select*fromt_test4
4whererownum<=25;
DIP1921-NOV-05
OUTLN1121-NOV-05
PUBLIC9999918-JUL-07
SYS021-NOV-05
SYSMAN3221-NOV-05
system521-NOV-05
6rowsselected.
3.2ROWNUM和Order BY
要注意的是：在使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM，下面OBJECT_ID是表T_TEST1的主键字段：
2whererownum<=5
3orderbyobject_id;
2C_OBJ#
3I_OBJ#
4TAB$
5CLU$
6C_TS#
但是，对非主键字段OBJECT_NAME进行排序时，结果就混乱了：
3orderbyobject_name;
28CON$
29C_COBJ#
20ICOL$
44I_USER1
15UNDO$
sql>selectcount(*)fromt_test1
2whereobject_name<'CON$';
21645
出现这种混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。
如果需要对非主键字段排序再去前n条数据，我们可以以以下方式实现：
3orderbyobject_name)
4whererownum<=5;
35489/1000e8d1_LinkedHashMapValueIt
35490/1000e8d1_LinkedHashMapValueIt
21801/1005bd30_LnkdConstant
21802/1005bd30_LnkdConstant
17205/10076b23_OraCustomDatumClosur
3.3排序分页
当对存在重复值的字段排序后再分页输出，我们很容易会陷入到另外一个“陷阱”。
请看以下例子，我们希望对T_TEST1的OWNER字段排序后，以每页输出10个结果的方式分页输出：
sql>selectowner,object_namefrom
2(selecta.*,rownumasrnfrom
3(selectowner,object_namefromt_test1orderbyowner)a
4whererownum<=10)
5wherern>=1;
OWNEROBJECT_NAME
------------------------------------------------------------
AFWOWNERAFWADAPTER
AFWOWNERAFWADAPTERCONFIGURATION
AFWOWNERAFWADAPTERCONFIGURATION_IDX1
AFWOWNERAFWADAPTERFQN_PK
AFWOWNERAFWADAPTERCONFIGURATION_PK
AFWOWNERAFWADAPTERCONFIGURATION_IDX2
AFWOWNERAFWSERVERCODE_PK
AFWOWNERAFWSERVER
AFWOWNERAFWADAPTERLOOKUP_IDX1
AFWOWNERAFWADAPTERLOOKUP
10rowsselected.
4whererownum<=20)
5wherern>=11;
AFWOWNERAFWTOKENSTATUSCODE_PK
AFWOWNERAFWTOKENSTATUS
AFWOWNERAFWTOKENADMIN_IDX1
AFWOWNERAFWTOKENADMINCODE_PK
AFWOWNERAFWTOKENADMIN
AFWOWNERAFWTOKEN
AFWOWNERAFWSERVERCONFIGURATION_PK
AFWOWNERAFWSERVERCONFIGURATION
10rowsselected.
仔细比较结果，你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的，说明这个输出结果是错误的，我们又陷入了一个“陷阱”。这是怎么回事呢，请先看下上述语句的查询计划：
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=205Card=20Bytes=94
0)
10VIEW(Cost=205Card=20Bytes=940)
32VIEW(Cost=205Card=30670Bytes=1042780)
43SORT(ORDERBYSTOPKEY)(Cost=205Card=30670Bytes=858760)
54tableACCESS(FULL)OF'T_TEST1'(Cost=42Card=30670Bytes=858760)
看下这个“陷阱”是怎么形成的。从查询计划上，我们可以注意到，对于子查询
selecta.*,153);">(selectowner,153);">whererownum<=20
优化器采用了“SORT (ORDER BY STOPKEY)”。
“SORT (ORDER BY STOPKEY)”不需要对所有数据进行排序，而是只要找出结果集中的按特定顺序的最前N条记录，一旦找出了这N条记录，就无需再对剩下的数据进行排序，而直接返回结果。这种算法我们可以视为是“快速排序”算法的变种。快速排序算法的基本思想是：先将数据分2组集合，保证第一集合中的每个数据都大于第二个集合中每个数据，然后再按这个原则对每个集合进行递归分组，直到集合的单位最小。在进行“SORT (ORDER BY STOPKEY)”时，首先找出N条数据（这些数据并没有做排序）放在第一组，保证第一组的数据都大于第二组的数据，然后只对第一组数据进行递归。
可以看到，基于这样的算法基础上，如果N的数值不同，数据的分组也不同（如N=20时，第一次分组比例为12:8，然后继续递归；当N=10时，第一次分组比例为3:7 … …），这样，在数据的排序字段值都相等时，输出结果的顺序就会因为N值不同而不同。
知道原因后，我们可以通过以下几种方法来避免这个“陷阱”。
1、让查询计划避免“SORT (ORDER BY STOPKEY)”，采用“SORT (ORDER BY)”，使数据排序不受ROWNUM的影响。但这样会使所有数据都做排序：
4wherern<=10
5andrn>=1;
AFWOWNERAFWSERVERCONFIGURATION_IDX1
AFWOWNERAFWTOKENTYPECODE_PK
4wherern<=20
5andrn>=11;
AFWOWNERAFWTOKENTYPE
AFWOWNERAFWTOKEN_PK
AFWOWNERAFWTOKEN_IDX6
sql>setautottrace
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=237Card=30670Bytes=1441490)
10VIEW(Cost=237Card=30670Bytes=1441490)
32VIEW(Cost=237Card=30670Bytes=1042780)
43SORT(ORDERBY)(Cost=237Card=30670Bytes=1073450)
54tableACCESS(FULL)OF'T_TEST1'(Cost=42Card=30670Bytes=1073450)
2、在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：
AFWOWNERAFWTOKEN_IDX1
AFWOWNERAFWTOKEN_IDX2
AFWOWNERAFWTOKEN_IDX3
AFWOWNERAFWTOKEN_IDX4
AFWOWNERAFWTOKEN_IDX5
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=253Card=20Bytes=940)
10VIEW(Cost=253Card=20Bytes=940)
32VIEW(Cost=253Card=30670Bytes=1042780)
43SORT(ORDERBYSTOPKEY)(Cost=253Card=30670Bytes=1196130)
54tableACCESS(FULL)OF'T_TEST1'(Cost=42Card=306
70Bytes=1196130)
3、对排序字段建立索引，并强制使用索引。这样就能利用索引已经建立好的排序结果：
sql>createindext_test1_idx1ont_test1(owner);
Indexcreated.
3(select/*+index(tT_TEST1_IDX1)*/owner,object_namefromt_test1torderbyowner)a
5wherern>=1
6;
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=414Card=20Bytes=940)
10VIEW(Cost=414Card=20Bytes=940)
32VIEW(Cost=414Card=30670Bytes=1042780)
43tableACCESS(BYINDEXROWID)OF'T_TEST1'(Cost=414Card=30670Bytes=858760)
54INDEX(FULLSCAN)OF'T_TEST1_IDX1'(NON-UNIQUE)(
Cost=26Card=30670)
以上就是ROWNUM的使用技巧及其注意事项，希望编程成员正确使用ROWNUM，也希望DBA遇到相关问题能迅速定位。
原文链接：https://www.f2er.com/oracle/210764.html