http://www.blogjava.net/conans/articles/219693.html
ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。
1 特殊结果输出
利用ROWNUM，我们可以做到一些特殊方式的输出。
1.1 Top N结果输出
我们如果希望取输出结果的前面几条数据，通过ROWNUM可以轻松实现：
sql> select * from t_test4 2 where rownum <= 5; USERNAME USER_ID CREATED------------------------------ ---------- ---------WOW 71 26-APR-07CS2 70 15-JAN-073 69 01-NOV-06DMP 68 12-OCT-06PROFILER 67 05-SEP-06
但是，如果你希望对一个排序结果取Top N数据的话，使用ROWNUM存在一些“陷阱”，我们后面部分会介绍这些“陷阱”并且说明如何避免。
1.2 分页查询
利用ROWNUM对结果进行分页，下面返回结果中的第6到第10条记录：
sql> select * from 2 ( 3 select a.*, rownum as rn from css_bl_view a 4 where capture_phone_num = '(1) 925-4604800' 5 ) b 6 where b.rn between 6 and 10; 6 rows selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=2183 Bytes =7166789) 1 0 VIEW (Cost=2770 Card=2183 Bytes=7166789) 2 1 COUNT 3 2 table ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 29346 consistent gets 29190 physical reads 0 redo size 7328 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed
另外一种实现方式：
sql> select * from css_bl_view a 2 where capture_phone_num = '(1) 925-4604800' 3 and rownum <= 10 4 minus 5 select * from css_bl_view a 6 where capture_phone_num = '(1) 925-4604800' 7 and rownum <= 5 8 ; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5920 Card=10 Bytes=8970) 1 0 MINUS 2 1 SORT (UNIQUE) (Cost=2960 Card=10 Bytes=5980) 3 2 COUNT (STOPKEY) 4 3 TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434) 5 1 SORT (UNIQUE) (Cost=2960 Card=5 Bytes=2990) 6 5 COUNT (STOPKEY) 7 6 table ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 62 consistent gets 50 physical reads 0 redo size 7232 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 5 rows processed
第三种实现方式：
sql> select * from 2 ( 3 select a.*, rownum as rn from css_bl_view a 4 where capture_phone_num = '(1) 925-4604800' 5 and rownum <= 10 6 ) b 7 where b.rn > 5; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=10 Bytes=32830) 1 0 VIEW (Cost=2770 Card=10 Bytes=32830) 2 1 COUNT (STOPKEY) 3 2 table ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 35 consistent gets 30 physical reads 0 redo size 7271 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed
这里特地将三种实现方式的查询计划及统计数据打印出来，大家可以比较一下3中方式的性能。
1.3 利用ROWNUM做分组子排序
对于以下表T_TEST4的内容：
OWNER NAME------------------------------------------------------STRMADMIN STREAMS_QUEUEAPARKMAN JOB_QUEUESYS AQ$_AQ_SRVNTFN_TABLE_ESYS AQ$_KUPC$DATAPUMP_QUETAB_EAPARKMAN AQ$_JMS_TEXT_ESTRMADMIN AQ$_STREAMS_QUEUE_TABLE_ESYS AQ$_SCHEDULER$_EVENT_QTAB_E…
如果我们希望结果按照OWNER进行分组后，再对每组中成员进行编号，结果类似如下：
OWNER NO NAME------------------------------------------------------APARKMAN 1 JOB_QUEUE 2 AQ$_JMS_TEXT_ESTRMADMIN 1 STREAMS_QUEUE 2 AQ$_STREAMS_QUEUE_TABLE_ESYS 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_ESTRMADMIN 1 STREAMS_QUEUE 2 AQ$_STREAMS_QUEUE_TABLE_ESYS 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_QSYSMAN 1 AQ$_MGMT_NOTIFY_QTABLE_E 2 MGMT_NOTIFY_Qsystem 1 DEF$_AQERROR 2 DEF$_AQCALL 3 AQ$_DEF$_AQERROR_E 4 AQ$_DEF$_AQCALL_EWMSYS 1 AQ$_WM$EVENT_QUEUE_TABLE_E 2 WM$EVENT_QUEUE 29 rows selected.2 性能
我们很多程序员在确认某个表中是否有相应数据时，喜欢加上ROWNUM=1，其思路就是只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。但是在10G之前，使用ROWNUM=1是不能达到预期的性能效果的，而是需要通过<2或<=1作为过滤条件才能达到预期效果，看以下查询计划：
sql> select * from t_test1 2 where object_id <100 3 and rownum = 1; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86) 1 0 COUNT (STOPKEY) 2 1 table ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654) 3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 62 consistent gets 0 physical reads 0 redo size 654 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed sql> select * from t_test1 2 where object_id <100 3 and rownum <= 1; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86) 1 0 COUNT (STOPKEY) 2 1 table ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654) 3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 654 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed sql> / Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86) 1 0 COUNT (STOPKEY) 2 1 table ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654) 3 2 INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 654 bytes sent via sql*Net to client 234 bytes received via sql*Net from client 4 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
10G以后，这个问题就被修正了：
sql> select * from t_test1 2 where rownum = 1; Execution Plan----------------------------------------------------------Plan hash value: 536364188 ------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | || 2 | table ACCESS FULL| T_TEST1 | 1 | 86 | 2 (0)| 00:00:01 |------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM=1) Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 1 physical reads 0 redo size 1201 bytes sent via sql*Net to client 385 bytes received via sql*Net from client 2 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed sql> select * from t_test1 2 where rownum <= 1; Execution Plan----------------------------------------------------------Plan hash value: 536364188 ------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 86 | 2 (0)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | || 2 | table ACCESS FULL| T_TEST1 | 1 | 86 | 2 (0)| 00:00:01 |------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=1) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1201 bytes sent via sql*Net to client 385 bytes received via sql*Net from client 2 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
3 ROWNUM的使用“陷阱”
由于ROWNUM是一个伪列，只有有结果记录时，ROWNUM才有相应数据，因此对它的使用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。
3.1 对ROWNUM进行>、>=、=操作
不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果
sql> select count(*) from css_bl_view a where rownum>0; COUNT(*)----------361928 sql> select count(*) from css_bl_view a 2 where rownum > 1; COUNT(*)---------- 0
这是因为：
1、ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；
2、返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1;
这样，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。上述查询可以通过子查询来替代：
sql> select count(*) 2 from 3 (select BL_REF_CDE, rownum rn from css_bl_view) 4 where rn > 1; COUNT(*)---------- 361927
我们可以通过以下方式来实现对ROWNUM的>、=的查询：
查询ROWNUM=5的数据：
sql> select object_id,object_name 2 from (select object_id,object_name, rownum as rn from t_test1) 3 where rn = 5; OBJECT_ID OBJECT_NAME---------- ------------------------------ 29 C_COBJ#
查询ROWNUM > 25的数据：
sql> select * from t_test4 2 minus 3 select * from t_test4 4 where rownum <= 25; USERNAME USER_ID CREATED------------------------------ ---------- ---------DIP 19 21-NOV-05OUTLN 11 21-NOV-05PUBLIC 99999 18-JUL-07SYS 0 21-NOV-05SYSMAN 32 21-NOV-05system 5 21-NOV-05 6 rows selected.3.2 ROWNUM和Order BY
要注意的是：在使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM，下面OBJECT_ID是表T_TEST1的主键字段：
sql> select object_id,object_name from t_test1 2 where rownum <= 5 3 order by object_id; OBJECT_ID OBJECT_NAME---------- ------------------------------ 2 C_OBJ# 3 I_OBJ# 4 TAB$ 5 CLU$ 6 C_TS#
但是，对非主键字段OBJECT_NAME进行排序时，结果就混乱了：
sql> select object_id,object_name from t_test1 2 where rownum <= 5 3 order by object_name; OBJECT_ID OBJECT_NAME---------- ------------------------------ 28 CON$ 29 C_COBJ# 20 ICOL$ 44 I_USER1 15 UNDO$ sql> select count(*) from t_test1 2 where object_name < 'CON$'; COUNT(*)---------- 21645
出现这种混乱的原因是：oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。
如果需要对非主键字段排序再去前n条数据，我们可以以以下方式实现：
sql> select object_id,object_name 2 from (select object_id,object_name from t_test1 3 order by object_name) 4 where rownum <= 5; OBJECT_ID OBJECT_NAME---------- ------------------------------ 35489 /1000e8d1_LinkedHashMapValueIt 35490 /1000e8d1_LinkedHashMapValueIt 21801 /1005bd30_LnkdConstant 21802 /1005bd30_LnkdConstant 17205 /10076b23_OraCustomDatumClosur
3.3 排序分页
当对存在重复值的字段排序后再分页输出，我们很容易会陷入到另外一个“陷阱”。
请看以下例子，我们希望对T_TEST1的OWNER字段排序后，以每页输出10个结果的方式分页输出：
sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name from t_test1 order by owner) a 4 where rownum <= 10) 5 where rn >= 1; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWSERVERCODE_PKAFWOWNER AFWSERVERAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWADAPTERLOOKUP 10 rows selected. sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name from t_test1 order by owner) a 4 where rownum <= 20) 5 where rn >= 11; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERAFWOWNER AFWADAPTERLOOKUP 10 rows selected.
仔细比较结果，你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的，说明这个输出结果是错误的，我们又陷入了一个“陷阱”。这是怎么回事呢，请先看下上述语句的查询计划：
sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name from t_test1 order by owner) a 4 where rownum <= 20) 5 where rn >= 11; 10 rows selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=94 0) 1 0 VIEW (Cost=205 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=205 Card=30670 Bytes=1042780) 4 3 SORT (ORDER BY STOPKEY) (Cost=205 Card=30670 Bytes=858760) 5 4 table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=858760)
看下这个“陷阱”是怎么形成的。从查询计划上，我们可以注意到，对于子查询
select a.*, rownum as rn from (select owner, object_name from t_test1 order by owner) awhere rownum <= 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的影响。但这样会使所有数据都做排序：
sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner) a) 4 where rn <= 10 5 and rn >= 1; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERCODE_PKAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWTOKENTYPECODE_PK 10 rows selected. sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner) a) 4 where rn <= 20 5 and rn >= 11; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENTYPEAFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWTOKEN_PKAFWOWNER AFWTOKEN_IDX6 10 rows selected. sql> set autot tracesql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner) a) 4 where rn <= 20 5 and rn >= 11; 10 rows selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=237 Card=30670 Bytes=1441490) 1 0 VIEW (Cost=237 Card=30670 Bytes=1441490) 2 1 COUNT 3 2 VIEW (Cost=237 Card=30670 Bytes=1042780) 4 3 SORT (ORDER BY) (Cost=237 Card=30670 Bytes=1073450) 5 4 table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=1073450)
2、在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：
sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a 4 where rownum <= 10) 5 where rn >= 1; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERLOOKUPAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERAFWOWNER AFWSERVERCODE_PK 10 rows selected. sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a 4 where rownum <= 20) 5 where rn >= 11; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWTOKENAFWOWNER AFWTOKEN_PKAFWOWNER AFWTOKEN_IDX1AFWOWNER AFWTOKEN_IDX2AFWOWNER AFWTOKEN_IDX3AFWOWNER AFWTOKEN_IDX4AFWOWNER AFWTOKEN_IDX5 10 rows selected. sql> set autot tracesql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a 4 where rownum <= 20) 5 where rn >= 11; 10 rows selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=253 Card=20 Bytes=940) 1 0 VIEW (Cost=253 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=253 Card=30670 Bytes=1042780) 4 3 SORT (ORDER BY STOPKEY) (Cost=253 Card=30670 Bytes=1196130) 5 4 table ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=306 70 Bytes=1196130)
3、对排序字段建立索引，并强制使用索引。这样就能利用索引已经建立好的排序结果：
sql> create index t_test1_idx1 on t_test1(owner); Index created. sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a 4 where rownum <= 10) 5 where rn >= 1 6 ; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUPAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERAFWOWNER AFWSERVERCODE_PK 10 rows selected. sql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a 4 where rownum <= 20) 5 where rn >= 11; OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWTOKENAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENTYPE 10 rows selected. sql> set autot tracesql> select owner, object_name from 2 (select a.*, rownum as rn from 3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a 4 where rownum <= 20) 5 where rn >= 11; 10 rows selected. Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=20 Bytes=940) 1 0 VIEW (Cost=414 Card=20 Bytes=940) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=414 Card=30670 Bytes=1042780) 4 3 table ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=414Card=30670 Bytes=858760) 5 4 INDEX (FULL SCAN) OF 'T_TEST1_IDX1' (NON-UNIQUE) ( Cost=26 Card=30670)
以上就是ROWNUM的使用技巧及其注意事项，希望编程成员正确使用ROWNUM，也希望DBA遇到相关问题能迅速定位。
相关推荐
ROWNUM 是 Oracle 中的一种伪列，它可以根据返回记录生成一个序列化的数字。利用 ROWNUM，我们可以生产一些原先难以实现的结果输出，但是因为它是伪列的特殊性，在使用时需要注意一些事项，以免掉入“陷阱”。 特殊...
例如，要获取第N到M条记录，可以先选择`WHERE ROWNUM ，然后在外部查询中使用`WHERE ROWNUM > N`。另外，也可以使用`BETWEEN N AND M`来获取相同的结果。 3. **陷阱与注意事项**：ROWNUM的生成是基于查询的执行计划...
在 Oracle 中，我们可以使用 ROWNUM 来查找前 N 个数据。例如，如果我们想要找到学生表中第一条学生的信息，可以使用 ROWNUM=1 作为条件。 ```sql SQL> select rownum,id,name from student where rownum=1; ``` ...
注意：在Oracle 12c及更高版本中，可以使用`FETCH FIRST N ROWS ONLY`来代替`ROWNUM`进行分页。 总结，`ROWID`和`ROWNUM`在Oracle SQL中各自扮演着不可或缺的角色。`ROWID`提供了直接访问数据的快速途径，而`ROWNUM...
### Oracle SQL中ROWID与ROWNUM的使用 #### ROWNUM 的使用——TOP-N 分析 在 Oracle SQL 中，`ROWNUM` 是一种特殊的伪列，用于标识查询结果集中的行编号。它常用于实现 TOP-N 查询，即返回结果集中满足特定条件的...
如果在联接查询中使用`ROWNUM`，它会在每个表的子查询中独立计算，而不是在整个结果集中。为了在联接查询中应用`ROWNUM`，通常需要将其与子查询或临时表结合使用。 最后，`ROWNUM`对于优化查询性能也有一定帮助。...
书中可能会介绍ROWNUM、RANK()、DENSE_RANK()等函数的使用，以及如何避免全表扫描。 6. **聚合函数和GROUP BY**：优化GROUP BY和HAVING子句，可能包括使用分析函数（如Lag(), Lead(), Rank()等）来减少数据处理，...
在 Oracle 中，如果你想要获取表中的第一条记录，你可以使用如下的 `ROWNUM` 写法： ```sql SELECT * FROM t WHERE ROWNUM = 1 ORDER BY id; ``` 而在 MySQL 中，要达到相同的效果，你需要使用 `LIMIT` 关键字配合...
`rownum` 是 Oracle 数据库中的一个特殊字段，主要用于给查询结果集中的每一行分配一个唯一的序号，该序号从 1 开始递增。`rownum` 的主要用途之一是用于实现分页查询，即控制查询结果的数量。 #### 二、rownum与...
- **使用LIMIT/ROWNUM**：对于大数据量的查询，限制返回结果的数量可以有效减少资源消耗。 #### 三、实际案例分析 ##### 3.1 案例背景 假设有一个销售订单系统，包含订单表(Order)和客户表(Customer)。订单表包含...
通过以上介绍，我们可以看到在Oracle中使用`OFFSET`与`FETCH FIRST`结合`ROWNUM`可以有效地实现分页查询。这种方法不仅简单易用，而且能够满足大多数场景下的需求。当然，在实际应用中还需要根据具体情况进行调整和...
然而，ROWNUM有一些限制，比如它不能与ORDER BY子句一起使用，除非在子查询中使用。因为ROWNUM的分配是在查询执行时动态完成的，不考虑排序顺序。 在MLDN魔乐科技的Oracle课堂19中，讲师会详细解释这两个概念的实际...
【标题】"MLDN魔乐科技JAVA培训_Oracle课堂19_修改约束、ROWNUM.rar"涉及的是Java编程与Oracle数据库管理的相关知识，主要聚焦在如何在Oracle数据库中修改约束以及使用ROWNUM函数进行数据筛选。 【描述】描述中提到...
6. **分页查询优化**：ROWNUM、ROWNUMBER()、FETCH NEXT和OFFSET/FETCH等方法在分页查询中的应用，以及如何避免全表扫描。 7. **存储过程和游标优化**：理解存储过程的执行机制，避免在循环中大量操作数据库，以及...
Oracle中通常使用`ROWNUM`和`ORDER BY`结合的方式实现`SELECT TOP N`的功能。首先对数据进行排序，然后通过`ROWNUM`限制返回的行数。例如，如果我们有一个名为`CUSTOMER`的表，想按`NAME`字段的字母顺序获取前三个...
2. 避免在GROUP BY后的HAVING子句中使用复杂的函数。 案例：`SELECT col1, COUNT(*) FROM table GROUP BY col1 HAVING COUNT(*) > 10`，可创建物化视图预先统计每个col1的记录数，然后直接查询。 七、注意SQL语句...
在IT行业中，数据库管理是至关重要的，而Oracle作为一款广泛使用的商业级数据库系统，其功能强大且灵活性高。本文将围绕"Oracle常用代码技巧"这一主题，深入探讨Java如何调用Oracle的存储过程以及Oracle实现分页的...
- **解析**: Oracle使用`rownum`关键字来限制返回的结果集行数。相比之下，DB2采用了`fetch first`语法，这使得指定结果集中的前几行变得更加直观和简单。 ##### 2. 获取系统日期 **Oracle**: `Select sysdate ...
### 二、Oracle中的抽样技巧 Oracle提供了内置的`SAMPLE`函数，可以直接用于实现抽样。例如： ```sql SELECT * FROM (SELECT * FROM AS SAMPLE(0.01)) WHERE ROWNUM ; ``` 上述SQL语句将从表`AS`中抽取1%的记录，...
而在Oracle中，如果要取得按某个字段排序的第M到N条记录，可以使用如下的嵌套查询： ```sql SELECT ID FROM ( SELECT ID, ROWNUM AS con FROM ( SELECT ID FROM TestSort ORDER BY ID ) WHERE rownum * N值 */ ...