oracle:rownum 的使用技巧

发布时间：2020-12-12 15:25:18 所属栏目：百科来源：网络整理

导读：声明：本文转载出自：http://www.blogjava.net/conans/articles/219693.html ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项

声明：

本文转载出自：http://www.blogjava.net/conans/articles/219693.html

ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。

1特殊结果输出

利用ROWNUM，我们可以做到一些特殊方式的输出。

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(*)

----------

sql>selectcount(*)fromcss_bl_viewa

2whererownum>1;

这是因为：

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;

我们可以通过以下方式来实现对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$';

出现这种混乱的原因是：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遇到相关问题能迅速定位。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!