声明:
ROWNUM是一种伪列,它会根据返回记录生成一个序列化的数字。利用ROWNUM,我们可以生产一些原先难以实现的结果输出,但因为它是伪列的这个特殊性,我们在使用时也需要注意一些事项,不要掉入“陷阱”。下面就介绍一下它的使用技巧及注意事项。
1特殊结果输出
ROWNUM,我们可以做到一些特殊方式的输出。
1.1Top N结果输出
我们如果希望取输出结果的前面几条数据,通过ROWNUM可以轻松实现:
sql> select * from t_test42 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 * from2 (3 select a.*, rownum as rn from css_bl_view a4 where capture_phone_num = '(1) 925-4604800'5 ) b6 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 COUNT3 2 table ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)Statistics----------------------------------------------------------0 recursive calls0 db block gets29346 consistent gets29190 physical reads0 redo size7328 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)5 rows processed
另外一种实现方式:
sql> select * from css_bl_view a2 where capture_phone_num = '(1) 925-4604800'3 and rownum <= 104 minus5 select * from css_bl_view a6 where capture_phone_num = '(1) 925-4604800'7 and rownum <= 58 ;Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5920 Card=10 Bytes=8970)1 0 MINUS2 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 calls0 db block gets62 consistent gets50 physical reads0 redo size7232 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client2 sorts (memory)0 sorts (disk)5 rows processed
第三种实现方式:
sql> select * from2 (3 select a.*, rownum as rn from css_bl_view a4 where capture_phone_num = '(1) 925-4604800'5 and rownum <= 106 ) b7 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 calls0 db block gets35 consistent gets30 physical reads0 redo size7271 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)5 rows processed
这里特地将三种实现方式的查询计划及统计数据打印出来,大家可以比较一下3中方式的性能。
1.3ROWNUM做分组子排序
对于以下表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_QUEUE2 AQ$_JMS_TEXT_ESTRMADMIN 1 STREAMS_QUEUE2 AQ$_STREAMS_QUEUE_TABLE_ESYS 1 AQ$_AQ_SRVNTFN_TABLE_E2 AQ$_KUPC$DATAPUMP_QUETAB_E3 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.name2 FROM (SELECT *3 FROM t_test84 ORDER BY owner, name ) a,5 (SELECT owner, MIN(rownum) min_sno6 FROM( SELECT *7 FROM t_test88 ORDER BY owner, name)9 GROUP BY owner) b10 WHERE a.owner=b.owner;OWNER SNO NAME------------------------------ ---------- ------------------------------APARKMAN 1 JOB_QUEUE2 AQ$_JMS_TEXT_ESTRMADMIN 1 STREAMS_QUEUE2 AQ$_STREAMS_QUEUE_TABLE_ESYS 1 AQ$_AQ_SRVNTFN_TABLE_E2 AQ$_KUPC$DATAPUMP_QUETAB_E3 AQ$_SCHEDULER$_EVENT_QTAB_E4 AQ$_SCHEDULER$_JOBQTAB_E5 AQ$_STREAMS_QUEUE_TABLE_E6 AQ$_SYS$SERVICE_METRICS_TAB_E7 AQ$_AQ_EVENT_TABLE_E8 AQ$_AQ$_MEM_MC_E9 AQ$_ALERT_QT_E10 ALERT_QUE11 AQ_EVENT_TABLE_Q12 SYS$SERVICE_METRICS13 STREAMS_QUEUE14 SRVQUEUE15 SCHEDULER$_JOBQ16 SCHEDULER$_EVENT_QUEUE17 AQ_SRVNTFN_TABLE_QSYSMAN 1 AQ$_MGMT_NOTIFY_QTABLE_E2 MGMT_NOTIFY_Qsystem 1 DEF$_AQERROR2 DEF$_AQCALL3 AQ$_DEF$_AQERROR_E4 AQ$_DEF$_AQCALL_EWMSYS 1 AQ$_WM$EVENT_QUEUE_TABLE_E2 WM$EVENT_QUEUE29 rows selected.
2性能
我们很多程序员在确认某个表中是否有相应数据时,喜欢加上ROWNUM=1,其思路就是只要存在一条数据就说明有相应数据,查询就可以直接返回了,这样就能提高性能了。但是在10G之前,使用ROWNUM=1是不能达到预期的性能效果的,而是需要通过<2或<=1作为过滤条件才能达到预期效果,看以下查询计划:
sql> select * from t_test12 where object_id <1003 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 calls0 db block gets62 consistent gets0 physical reads0 redo size654 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedsql> select * from t_test12 where object_id <1003 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 calls0 db block gets3 consistent gets0 physical reads0 redo size654 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedsql> /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 calls0 db block gets3 consistent gets0 physical reads0 redo size654 bytes sent via sql*Net to client234 bytes received via sql*Net from client4 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
10G以后,这个问题就被修正了:
sql> select * from t_test12 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 calls0 db block gets4 consistent gets1 physical reads0 redo size1201 bytes sent via sql*Net to client385 bytes received via sql*Net from client2 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedsql> select * from t_test12 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 calls0 db block gets4 consistent gets0 physical reads0 redo size1201 bytes sent via sql*Net to client385 bytes received via sql*Net from client2 sql*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
3ROWNUM的使用“陷阱”
由于ROWNUM是一个伪列,只有有结果记录时,ROWNUM才有相应数据,因此对它的使用不能向普通列那样使用,否则就会陷入一些“陷阱”当中。
3.1对ROWNUM进行>、>=、=操作
不能对ROWNUM使用>(大于1的数值)、>=(大于或等于1的数值)、=(大于或等于1的数值),否则无结果
sql> select count(*) from css_bl_view a where rownum>0;COUNT(*)----------361928sql> select count(*) from css_bl_view a2 where rownum > 1;COUNT(*)----------0
这是因为:
1、ROWNUM是伪列,必须要要有返回结果后,每条返回记录就会对应产生一个ROWNUM数值;
2、返回结果记录的ROWNUM是从1开始排序的,因此第一条始终是1;
这样,当查询到第一条记录时,该记录的ROWNUM为1,但条件要求ROWNUM>1,因此不符合,继续查询下一条;因为前面没有符合要求的记录,因此下一条记录过来后,其ROWNUM还是为1,如此循环,就不会产生结果。上述查询可以通过子查询来替代:
sql> select count(*)2 from3 (select BL_REF_CDE, rownum rn from css_bl_view)4 where rn > 1;COUNT(*)----------361927
我们可以通过以下方式来实现对ROWNUM的>、=的查询:
查询ROWNUM=5的数据:
sql> select object_id,object_name2 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_test42 minus3 select * from t_test44 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-056 rows selected.
3.2ROWNUM和Order BY
要注意的是:在使用ROWNUM时,只有当Order By的字段是主键时,查询结果才会先排序再计算ROWNUM,下面OBJECT_ID是表T_TEST1的主键字段:
sql> select object_id,object_name from t_test12 where rownum <= 53 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_test12 where rownum <= 53 order by object_name;OBJECT_ID OBJECT_NAME---------- ------------------------------28 CON$29 C_COBJ#20 ICOL$44 I_USER115 UNDO$sql> select count(*) from t_test12 where object_name
出现这种混乱的原因是:oracle先按物理存储位置(rowid)顺序取出满足rownum条件的记录,即物理位置上的前5条数据,然后在对这些数据按照Order By的字段进行排序,而不是我们所期望的先排序、再取特定记录数。
如果需要对非主键字段排序再去前n条数据,我们可以以以下方式实现:
sql> select object_id,object_name2 from (select object_id,object_name from t_test13 order by object_name)4 where rownum <= 5;OBJECT_ID OBJECT_NAME---------- ------------------------------35489 /1000e8d1_LinkedHashMapValueIt35490 /1000e8d1_LinkedHashMapValueIt21801 /1005bd30_LnkdConstant21802 /1005bd30_LnkdConstant17205 /10076b23_OraCustomDatumClosur
3.3排序分页
当对存在重复值的字段排序后再分页输出,我们很容易会陷入到另外一个“陷阱”。
请看以下例子,我们希望对T_TEST1的OWNER字段排序后,以每页输出10个结果的方式分页输出:
sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name from t_test1 order by owner) a4 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 AFWADAPTERLOOKUP10 rows selected.sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name from t_test1 order by owner) a4 where rownum <= 20)5 where rn >= 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWSERVERCONFIGURATIONAFWOWNER AFWSERVERAFWOWNER AFWADAPTERLOOKUP10 rows selected.
仔细比较结果,你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的,说明这个输出结果是错误的,我们又陷入了一个“陷阱”。这是怎么回事呢,请先看下上述语句的查询计划:
sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name from t_test1 order by owner) a4 where rownum <= 20)5 where rn >= 11;10 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=940)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 from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner) a)4 where rn <= 105 and rn >= 1;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERCODE_PKAFWOWNER AFWSERVERCONFIGURATION_IDX1AFWOWNER AFWTOKENTYPECODE_PK10 rows selected.sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner) a)4 where rn <= 205 and rn >= 11;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWTOKENTYPEAFWOWNER AFWTOKENSTATUSCODE_PKAFWOWNER AFWTOKENSTATUSAFWOWNER AFWTOKENADMIN_IDX1AFWOWNER AFWTOKENADMINCODE_PKAFWOWNER AFWTOKENADMINAFWOWNER AFWTOKENAFWOWNER AFWSERVERCONFIGURATION_PKAFWOWNER AFWTOKEN_PKAFWOWNER AFWTOKEN_IDX610 rows selected.sql> set autot tracesql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner) a)4 where rn <= 205 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 COUNT3 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 from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a4 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_PK10 rows selected.sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a4 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_IDX510 rows selected.sql> set autot tracesql> select owner, object_name from2 (select a.*, rownum as rn from3 (select owner, object_name, rowid from t_test1 order by owner, object_id) a4 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=30670 Bytes=1196130)
3、对排序字段建立索引,并强制使用索引。这样就能利用索引已经建立好的排序结果:sql> create index t_test1_idx1 on t_test1(owner);Index created.sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4 where rownum <= 10)5 where rn >= 16 ;OWNER OBJECT_NAME------------------------------ ------------------------------AFWOWNER AFWADAPTERAFWOWNER AFWADAPTERCONFIGURATIONAFWOWNER AFWADAPTERCONFIGURATION_IDX1AFWOWNER AFWADAPTERCONFIGURATION_IDX2AFWOWNER AFWADAPTERCONFIGURATION_PKAFWOWNER AFWADAPTERFQN_PKAFWOWNER AFWADAPTERLOOKUPAFWOWNER AFWADAPTERLOOKUP_IDX1AFWOWNER AFWSERVERAFWOWNER AFWSERVERCODE_PK10 rows selected.sql> select owner, object_name from2 (select a.*, rownum as rn from3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4 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 AFWTOKENTYPE10 rows selected.sql> set autot tracesql> select owner, object_name from2 (select a.*, rownum as rn from3 (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a4 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遇到相关问题能迅速定位。