`

Oracle中ROWNUM的使用技巧

阅读更多

Oracle中ROWNUM的使用技巧

 

 

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遇到相关问题能迅速定位。


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/nestashen/archive/2008/10/21/3117729.aspx

 

分享到:
评论

相关推荐

    ROWNUM的使用技巧

    ROWNUM 是 Oracle 中的一种伪列,它可以根据返回记录生成一个序列化的数字。利用 ROWNUM,我们可以生产一些原先难以实现的结果输出,但是因为它是伪列的特殊性,在使用时需要注意一些事项,以免掉入“陷阱”。 特殊...

    Oracle中ROWNUM的使用技巧.docx

    例如,要获取第N到M条记录,可以先选择`WHERE ROWNUM ,然后在外部查询中使用`WHERE ROWNUM &gt; N`。另外,也可以使用`BETWEEN N AND M`来获取相同的结果。 3. **陷阱与注意事项**:ROWNUM的生成是基于查询的执行计划...

    解析oracle的rownum

    在 Oracle 中,我们可以使用 ROWNUM 来查找前 N 个数据。例如,如果我们想要找到学生表中第一条学生的信息,可以使用 ROWNUM=1 作为条件。 ```sql SQL&gt; select rownum,id,name from student where rownum=1; ``` ...

    oracle_SQL中rowid与rownum的使用

    注意:在Oracle 12c及更高版本中,可以使用`FETCH FIRST N ROWS ONLY`来代替`ROWNUM`进行分页。 总结,`ROWID`和`ROWNUM`在Oracle SQL中各自扮演着不可或缺的角色。`ROWID`提供了直接访问数据的快速途径,而`ROWNUM...

    Oracle--ROWNUM.rar_oracle

    如果在联接查询中使用`ROWNUM`,它会在每个表的子查询中独立计算,而不是在整个结果集中。为了在联接查询中应用`ROWNUM`,通常需要将其与子查询或临时表结合使用。 最后,`ROWNUM`对于优化查询性能也有一定帮助。...

    oracle_SQL中ROWID与ROWNUM的使用

    ### Oracle SQL中ROWID与ROWNUM的使用 #### ROWNUM 的使用——TOP-N 分析 在 Oracle SQL 中,`ROWNUM` 是一种特殊的伪列,用于标识查询结果集中的行编号。它常用于实现 TOP-N 查询,即返回结果集中满足特定条件的...

    Oracle查询优化改写-技巧与案例

    书中可能会介绍ROWNUM、RANK()、DENSE_RANK()等函数的使用,以及如何避免全表扫描。 6. **聚合函数和GROUP BY**:优化GROUP BY和HAVING子句,可能包括使用分析函数(如Lag(), Lead(), Rank()等)来减少数据处理,...

    mysql类似oracle rownum写法实例详解

    在 Oracle 中,如果你想要获取表中的第一条记录,你可以使用如下的 `ROWNUM` 写法: ```sql SELECT * FROM t WHERE ROWNUM = 1 ORDER BY id; ``` 而在 MySQL 中,要达到相同的效果,你需要使用 `LIMIT` 关键字配合...

    rownum用法(不使用minus)

    `rownum` 是 Oracle 数据库中的一个特殊字段,主要用于给查询结果集中的每一行分配一个唯一的序号,该序号从 1 开始递增。`rownum` 的主要用途之一是用于实现分页查询,即控制查询结果的数量。 #### 二、rownum与...

    oracle查询优化改写 技巧与案例

    - **使用LIMIT/ROWNUM**:对于大数据量的查询,限制返回结果的数量可以有效减少资源消耗。 #### 三、实际案例分析 ##### 3.1 案例背景 假设有一个销售订单系统,包含订单表(Order)和客户表(Customer)。订单表包含...

    Oracle分页(limit方式的运用)

    通过以上介绍,我们可以看到在Oracle中使用`OFFSET`与`FETCH FIRST`结合`ROWNUM`可以有效地实现分页查询。这种方法不仅简单易用,而且能够满足大多数场景下的需求。当然,在实际应用中还需要根据具体情况进行调整和...

    MLDN魔乐科技_Oracle课堂19_修改约束、ROWNUM

    然而,ROWNUM有一些限制,比如它不能与ORDER BY子句一起使用,除非在子查询中使用。因为ROWNUM的分配是在查询执行时动态完成的,不考虑排序顺序。 在MLDN魔乐科技的Oracle课堂19中,讲师会详细解释这两个概念的实际...

    MLDN魔乐科技JAVA培训_Oracle课堂19_修改约束、ROWNUM.rar

    【标题】"MLDN魔乐科技JAVA培训_Oracle课堂19_修改约束、ROWNUM.rar"涉及的是Java编程与Oracle数据库管理的相关知识,主要聚焦在如何在Oracle数据库中修改约束以及使用ROWNUM函数进行数据筛选。 【描述】描述中提到...

    Oracle查询优化改写 技巧与案例

    6. **分页查询优化**:ROWNUM、ROWNUMBER()、FETCH NEXT和OFFSET/FETCH等方法在分页查询中的应用,以及如何避免全表扫描。 7. **存储过程和游标优化**:理解存储过程的执行机制,避免在循环中大量操作数据库,以及...

    如何在Oracle中实现SELECT_TOP_N的方法

    Oracle中通常使用`ROWNUM`和`ORDER BY`结合的方式实现`SELECT TOP N`的功能。首先对数据进行排序,然后通过`ROWNUM`限制返回的行数。例如,如果我们有一个名为`CUSTOMER`的表,想按`NAME`字段的字母顺序获取前三个...

    Oracle查询优化改写技巧与案例

    2. 避免在GROUP BY后的HAVING子句中使用复杂的函数。 案例:`SELECT col1, COUNT(*) FROM table GROUP BY col1 HAVING COUNT(*) &gt; 10`,可创建物化视图预先统计每个col1的记录数,然后直接查询。 七、注意SQL语句...

    Oracle常用代码技巧

    在IT行业中,数据库管理是至关重要的,而Oracle作为一款广泛使用的商业级数据库系统,其功能强大且灵活性高。本文将围绕"Oracle常用代码技巧"这一主题,深入探讨Java如何调用Oracle的存储过程以及Oracle实现分页的...

    利用ORACLE实现数据的抽样

    ### 二、Oracle中的抽样技巧 Oracle提供了内置的`SAMPLE`函数,可以直接用于实现抽样。例如: ```sql SELECT * FROM (SELECT * FROM AS SAMPLE(0.01)) WHERE ROWNUM ; ``` 上述SQL语句将从表`AS`中抽取1%的记录,...

    Oracle中实现MySql中limit+SqlServer分页+分页的存储过程

    而在Oracle中,如果要取得按某个字段排序的第M到N条记录,可以使用如下的嵌套查询: ```sql SELECT ID FROM ( SELECT ID, ROWNUM AS con FROM ( SELECT ID FROM TestSort ORDER BY ID ) WHERE rownum * N值 */ ...

    Oracle数据库学习指南

    Oracle2: 1. 《Oracle8 优化技术》摘录 (第一章 安装) 2. 《Oracle8 优化技术》摘录 (第二章 ...50. 怎样在SQLPlus中使用 '&' 来实现自定义参数变量? 51. 怎样在查询记录时给记录加锁 52. 自动备份Oracle数据库

Global site tag (gtag.js) - Google Analytics