Oracle中ROWNUM的使用技巧

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-02-14 14:54:36

分享到 

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-07 CS2                                    70 15-JAN-07 3                                      69 01-NOV-06 DMP                                    68 12-OCT-06 PROFILER                               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_QUEUE APARKMAN                                JOB_QUEUE SYS                                     AQ$_AQ_SRVNTFN_TABLE_E SYS                                     AQ$_KUPC$DATAPUMP_QUETAB_E APARKMAN                                AQ$_JMS_TEXT_E STRMADMIN                               AQ$_STREAMS_QUEUE_TABLE_E SYS                                     AQ$_SCHEDULER$_EVENT_QTAB_E …   如果我们希望结果按照OWNER进行分组后，再对每组中成员进行编号，结果类似如下：   OWNER                                   NO NAME ------------------------------------------------------ APARKMAN                                1 JOB_QUEUE                                         2 AQ$_JMS_TEXT_E STRMADMIN                               1 STREAMS_QUEUE                                         2 AQ$_STREAMS_QUEUE_TABLE_E SYS                                     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_E STRMADMIN                               1 STREAMS_QUEUE                                         2 AQ$_STREAMS_QUEUE_TABLE_E SYS                                     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_Q SYSMAN                                  1 AQ$_MGMT_NOTIFY_QTABLE_E                                         2 MGMT_NOTIFY_Q SYSTEM                                  1 DEF$_AQERROR                                         2 DEF$_AQCALL                                         3 AQ$_DEF$_AQERROR_E                                         4 AQ$_DEF$_AQCALL_E WMSYS                                   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-05 OUTLN                                  11 21-NOV-05 PUBLIC                              99999 18-JUL-07 SYS                                     0 21-NOV-05 SYSMAN                                 32 21-NOV-05 SYSTEM                                  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                       AFWADAPTER AFWOWNER                       AFWADAPTERCONFIGURATION AFWOWNER                       AFWADAPTERCONFIGURATION_IDX1 AFWOWNER                       AFWADAPTERFQN_PK AFWOWNER                       AFWADAPTERCONFIGURATION_PK AFWOWNER                       AFWADAPTERCONFIGURATION_IDX2 AFWOWNER                       AFWSERVERCODE_PK AFWOWNER                       AFWSERVER AFWOWNER                       AFWADAPTERLOOKUP_IDX1 AFWOWNER                       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_PK AFWOWNER                       AFWTOKENSTATUS AFWOWNER                       AFWTOKENADMIN_IDX1 AFWOWNER                       AFWTOKENADMINCODE_PK AFWOWNER                       AFWTOKENADMIN AFWOWNER                       AFWTOKEN AFWOWNER                       AFWSERVERCONFIGURATION_PK AFWOWNER                       AFWSERVERCONFIGURATION AFWOWNER                       AFWSERVER AFWOWNER                       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)