Oracle中ROWNUM的使用技巧

Oracle中ROWNUM的使用技巧 [English] 作者： fuyuncat 来源： www.HelloDBA.com 日期： 2009-02-14 14:54:36 ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，

Oracle中ROWNUM的使用技巧

[English]

作者：fuyuncat

来源：www.HelloDBA.com

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

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

1         特殊结果输出

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

1.1     Top N结果输出

我们如果希望取输出结果的前面几条数据，通过ROWNUM可以轻松实现：

 

<code>SQL> select * from t_test4</code>

<code>  2  where rownum </code>

<code> </code>

<code>USERNAME                          USER_ID CREATED</code>

<code>------------------------------ ---------- ---------</code>

<code>WOW                                    71 26-APR-07</code>

<code>CS2                                    70 15-JAN-07</code>

<code>3                                      69 01-NOV-06</code>

<code>DMP                                    68 12-OCT-06</code>

<code>PROFILER                               67 05-SEP-06</code>

 

但是，如果你希望对一个排序结果取Top N数据的话，使用ROWNUM存在一些“陷阱”，我们后面部分会介绍这些“陷阱”并且说明如何避免。

1.2     分页查询

利用ROWNUM对结果进行分页，下面返回结果中的第6到第10条记录：

<code>SQL> select * from</code>

<code>  2  (</code>

<code>  3  select a.*, rownum as rn from css_bl_view a</code>

<code>  4  where capture_phone_num = '(1) 925-4604800'</code>

<code>  5  ) b</code>

<code>  6  where b.rn between 6 and 10;</code>

<code> </code>

<code>6 rows selected.</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=2183 Bytes =7166789)</code>

<code>   1    0   VIEW (Cost=2770 Card=2183 Bytes=7166789)</code>

<code>   2    1     COUNT</code>

<code>   3    2       TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>      29346  consistent gets</code>

<code>      29190  physical reads</code>

<code>          0  redo size</code>

<code>       7328  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          5  rows processed</code>

 

另外一种实现方式：

 

<code>SQL> select * from css_bl_view a</code>

<code>  2  where capture_phone_num = '(1) 925-4604800'</code>

<code>  3  and rownum </code>

<code>  4  minus</code>

<code>  5  select * from css_bl_view a</code>

<code>  6  where capture_phone_num = '(1) 925-4604800'</code>

<code>  7  and rownum </code>

<code>  8  ;</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5920 Card=10 Bytes=8970)</code>

<code>   1    0   MINUS</code>

<code>   2    1     SORT (UNIQUE) (Cost=2960 Card=10 Bytes=5980)</code>

<code>   3    2       COUNT (STOPKEY)</code>

<code>   4    3         TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)</code>

<code>   5    1     SORT (UNIQUE) (Cost=2960 Card=5 Bytes=2990)</code>

<code>   6    5       COUNT (STOPKEY)</code>

<code>   7    6         TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>         62  consistent gets</code>

<code>         50  physical reads</code>

<code>          0  redo size</code>

<code>       7232  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          2  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          5  rows processed</code>

 

第三种实现方式：

 

<code>SQL> select * from</code>

<code>  2  (</code>

<code>  3  select a.*, rownum as rn from css_bl_view a</code>

<code>  4  where capture_phone_num = '(1) 925-4604800'</code>

<code>  5  and rownum </code>

<code>  6  ) b</code>

<code>  7  where b.rn > 5;</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2770 Card=10 Bytes=32830)</code>

<code>   1    0   VIEW (Cost=2770 Card=10 Bytes=32830)</code>

<code>   2    1     COUNT (STOPKEY)</code>

<code>   3    2       TABLE ACCESS (FULL) OF 'CSS_BL_VIEW' (Cost=2770 Card=2183 Bytes=1305434)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>         35  consistent gets</code>

<code>         30  physical reads</code>

<code>          0  redo size</code>

<code>       7271  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          5  rows processed</code>

 

这里特地将三种实现方式的查询计划及统计数据打印出来，大家可以比较一下3中方式的性能。

1.3     利用ROWNUM做分组子排序

对于以下表T_TEST4的内容：

 

<code>OWNER                                   NAME</code>

<code>------------------------------------------------------</code>

<code>STRMADMIN                               STREAMS_QUEUE</code>

<code>APARKMAN                                JOB_QUEUE</code>

<code>SYS                                     AQ$_AQ_SRVNTFN_TABLE_E</code>

<code>SYS                                     AQ$_KUPC$DATAPUMP_QUETAB_E</code>

<code>APARKMAN                                AQ$_JMS_TEXT_E</code>

<code>STRMADMIN                               AQ$_STREAMS_QUEUE_TABLE_E</code>

<code>SYS                                     AQ$_SCHEDULER$_EVENT_QTAB_E</code>

<code>…</code>

 

如果我们希望结果按照OWNER进行分组后，再对每组中成员进行编号，结果类似如下：

 

<code>OWNER                                   NO NAME</code>

<code>------------------------------------------------------</code>

<code>APARKMAN                                1 JOB_QUEUE</code>

<code>                                        2 AQ$_JMS_TEXT_E</code>

<code>STRMADMIN                               1 STREAMS_QUEUE</code>

<code>                                        2 AQ$_STREAMS_QUEUE_TABLE_E</code>

<code>SYS                                     1 AQ$_AQ_SRVNTFN_TABLE_E</code>

<code>                                        2 AQ$_KUPC$DATAPUMP_QUETAB_E</code>

<code>                                        3 AQ$_SCHEDULER$_EVENT_QTAB_E</code>

<code>…</code>

 

在没有ROWNUM时要实现这样的功能会很复杂，但通过ROWNUM我们可以轻松实现：

 

<code>SQL> SELECT DECODE(ROWNUM-min_sno,0,a.owner,NULL) owner,DECODE(ROWNUM-min_sno,0,1,rownum+1-min_sno) sno, a.name</code>

<code>  2  FROM (SELECT *</code>

<code>  3        FROM t_test8</code>

<code>  4        ORDER BY owner, name ) a,</code>

<code>  5       (SELECT owner, MIN(rownum) min_sno</code>

<code>  6        FROM( SELECT *</code>

<code>  7              FROM t_test8</code>

<code>  8              ORDER BY owner, name)</code>

<code>  9        GROUP BY owner) b</code>

<code> 10  WHERE a.owner=b.owner;</code>

<code> </code>

<code>OWNER                                 SNO NAME</code>

<code>------------------------------ ---------- ------------------------------</code>

<code>APARKMAN                                1 JOB_QUEUE</code>

<code>                                        2 AQ$_JMS_TEXT_E</code>

<code>STRMADMIN                               1 STREAMS_QUEUE</code>

<code>                                        2 AQ$_STREAMS_QUEUE_TABLE_E</code>

<code>SYS                                     1 AQ$_AQ_SRVNTFN_TABLE_E</code>

<code>                                        2 AQ$_KUPC$DATAPUMP_QUETAB_E</code>

<code>                                        3 AQ$_SCHEDULER$_EVENT_QTAB_E</code>

<code>                                        4 AQ$_SCHEDULER$_JOBQTAB_E</code>

<code>                                        5 AQ$_STREAMS_QUEUE_TABLE_E</code>

<code>                                        6 AQ$_SYS$SERVICE_METRICS_TAB_E</code>

<code>                                        7 AQ$_AQ_EVENT_TABLE_E</code>

<code>                                        8 AQ$_AQ$_MEM_MC_E</code>

<code>                                        9 AQ$_ALERT_QT_E</code>

<code>                                       10 ALERT_QUE</code>

<code>                                       11 AQ_EVENT_TABLE_Q</code>

<code>                                       12 SYS$SERVICE_METRICS</code>

<code>                                       13 STREAMS_QUEUE</code>

<code>                                       14 SRVQUEUE</code>

<code>                                       15 SCHEDULER$_JOBQ</code>

<code>                                       16 SCHEDULER$_EVENT_QUEUE</code>

<code>                                       17 AQ_SRVNTFN_TABLE_Q</code>

<code>SYSMAN                                  1 AQ$_MGMT_NOTIFY_QTABLE_E</code>

<code>                                        2 MGMT_NOTIFY_Q</code>

<code>SYSTEM                                  1 DEF$_AQERROR</code>

<code>                                        2 DEF$_AQCALL</code>

<code>                                        3 AQ$_DEF$_AQERROR_E</code>

<code>                                        4 AQ$_DEF$_AQCALL_E</code>

<code>WMSYS                                   1 AQ$_WM$EVENT_QUEUE_TABLE_E</code>

<code>                                        2 WM$EVENT_QUEUE</code>

<code> </code>

<code>29 rows selected.</code>

2         性能

我们很多程序员在确认某个表中是否有相应数据时，喜欢加上ROWNUM=1，其思路就是只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。但是在10G之前，使用ROWNUM=1是不能达到预期的性能效果的，而是需要通过

 

<code>SQL> select * from t_test1</code>

<code>  2  where object_id </code>

<code>  3  and rownum = 1;</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)</code>

<code>   1    0   COUNT (STOPKEY)</code>

<code>   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)</code>

<code>   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>         62  consistent gets</code>

<code>          0  physical reads</code>

<code>          0  redo size</code>

<code>        654  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          1  rows processed</code>

<code> </code>

<code>SQL> select * from t_test1</code>

<code>  2  where object_id </code>

<code>  3  and rownum </code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)</code>

<code>   1    0   COUNT (STOPKEY)</code>

<code>   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)</code>

<code>   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>          3  consistent gets</code>

<code>          0  physical reads</code>

<code>          0  redo size</code>

<code>        654  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          1  rows processed</code>

<code> </code>

<code>SQL> /</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=86)</code>

<code>   1    0   COUNT (STOPKEY)</code>

<code>   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_TEST1' (Cost=37 Card=89 Bytes=7654)</code>

<code>   3    2       INDEX (RANGE SCAN) OF 'T_TEST1_PK' (UNIQUE) (Cost=2 Card=89)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>          3  consistent gets</code>

<code>          0  physical reads</code>

<code>          0  redo size</code>

<code>        654  bytes sent via SQL*Net to client</code>

<code>        234  bytes received via SQL*Net from client</code>

<code>          4  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          1  rows processed</code>

 

10G以后，这个问题就被修正了：

 

<code>SQL> select * from t_test1</code>

<code>  2  where rownum = 1;</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>Plan hash value: 536364188</code>

<code> </code>

<code>------------------------------------------------------------------------------</code>

<code>| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |</code>

<code>------------------------------------------------------------------------------</code>

<code>|   0 | SELECT STATEMENT   |         |     1 |    86 |     2   (0)| 00:00:01 |</code>

<code>|*  1 |  COUNT STOPKEY     |         |       |       |            |          |</code>

<code>|   2 |   TABLE ACCESS FULL| T_TEST1 |     1 |    86 |     2   (0)| 00:00:01 |</code>

<code>------------------------------------------------------------------------------</code>

<code> </code>

<code>Predicate Information (identified by operation id):</code>

<code>---------------------------------------------------</code>

<code> </code>

<code>   1 - filter(ROWNUM=1)</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          1  recursive calls</code>

<code>          0  db block gets</code>

<code>          4  consistent gets</code>

<code>          1  physical reads</code>

<code>          0  redo size</code>

<code>       1201  bytes sent via SQL*Net to client</code>

<code>        385  bytes received via SQL*Net from client</code>

<code>          2  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          1  rows processed</code>

<code> </code>

<code>SQL> select * from t_test1</code>

<code>  2  where rownum </code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>Plan hash value: 536364188</code>

<code> </code>

<code>------------------------------------------------------------------------------</code>

<code>| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |</code>

<code>------------------------------------------------------------------------------</code>

<code>|   0 | SELECT STATEMENT   |         |     1 |    86 |     2   (0)| 00:00:01 |</code>

<code>|*  1 |  COUNT STOPKEY     |         |       |       |            |          |</code>

<code>|   2 |   TABLE ACCESS FULL| T_TEST1 |     1 |    86 |     2   (0)| 00:00:01 |</code>

<code>------------------------------------------------------------------------------</code>

<code> </code>

<code>Predicate Information (identified by operation id):</code>

<code>---------------------------------------------------</code>

<code> </code>

<code>   1 - filter(ROWNUM</code>

<code> </code>

<code> </code>

<code>Statistics</code>

<code>----------------------------------------------------------</code>

<code>          0  recursive calls</code>

<code>          0  db block gets</code>

<code>          4  consistent gets</code>

<code>          0  physical reads</code>

<code>          0  redo size</code>

<code>       1201  bytes sent via SQL*Net to client</code>

<code>        385  bytes received via SQL*Net from client</code>

<code>          2  SQL*Net roundtrips to/from client</code>

<code>          0  sorts (memory)</code>

<code>          0  sorts (disk)</code>

<code>          1  rows processed</code>

 

3         ROWNUM的使用“陷阱”

由于ROWNUM是一个伪列，只有有结果记录时，ROWNUM才有相应数据，因此对它的使用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。

3.1     对ROWNUM进行>、>=、=操作

不能对ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果

 

<code>SQL> select count(*) from css_bl_view a where rownum>0;</code>

<code> </code>

<code>  COUNT(*)</code>

<code>----------</code>

<code>361928</code>

<code> </code>

<code> </code>

<code>SQL> select count(*) from css_bl_view a</code>

<code>  2  where rownum > 1;</code>

<code> </code>

<code>  COUNT(*)</code>

<code>----------</code>

<code>         0</code>

 

这是因为：

1、ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；

2、返回结果记录的ROWNUM是从1开始排序的，因此第一条始终是1;

 

这样，当查询到第一条记录时，该记录的ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。上述查询可以通过子查询来替代：

 

<code>SQL> select count(*)</code>

<code>  2  from</code>

<code>  3  (select BL_REF_CDE, rownum rn from css_bl_view)</code>

<code>  4  where rn > 1;</code>

<code> </code>

<code>  COUNT(*)</code>

<code>----------</code>

<code>    361927</code>

 

我们可以通过以下方式来实现对ROWNUM的>、=的查询：

查询ROWNUM=5的数据：

 

<code>SQL> select object_id,object_name</code>

<code>  2  from (select object_id,object_name, rownum as rn from t_test1)</code>

<code>  3  where rn = 5;</code>

<code> </code>

<code> OBJECT_ID OBJECT_NAME</code>

<code>---------- ------------------------------</code>

<code>        29 C_COBJ#</code>

 

查询ROWNUM > 25的数据：

 

<code>SQL> select * from t_test4</code>

<code>  2  minus</code>

<code>  3  select * from t_test4</code>

<code>  4  where rownum </code>

<code> </code>

<code>USERNAME                          USER_ID CREATED</code>

<code>------------------------------ ---------- ---------</code>

<code>DIP                                    19 21-NOV-05</code>

<code>OUTLN                                  11 21-NOV-05</code>

<code>PUBLIC                              99999 18-JUL-07</code>

<code>SYS                                     0 21-NOV-05</code>

<code>SYSMAN                                 32 21-NOV-05</code>

<code>SYSTEM                                  5 21-NOV-05</code>

<code> </code>

<code>6 rows selected.</code>

3.2     ROWNUM和Order BY

要注意的是：在使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM，下面OBJECT_ID是表T_TEST1的主键字段：

 

<code>SQL> select object_id,object_name from t_test1</code>

<code>  2  where rownum </code>

<code>  3  order by object_id;</code>

<code> </code>

<code> OBJECT_ID OBJECT_NAME</code>

<code>---------- ------------------------------</code>

<code>         2 C_OBJ#</code>

<code>         3 I_OBJ#</code>

<code>         4 TAB$</code>

<code>         5 CLU$</code>

<code>         6 C_TS#</code>

 

但是，对非主键字段OBJECT_NAME进行排序时，结果就混乱了：

 

<code>SQL> select object_id,object_name from t_test1</code>

<code>  2  where rownum </code>

<code>  3  order by object_name;</code>

<code> </code>

<code> OBJECT_ID OBJECT_NAME</code>

<code>---------- ------------------------------</code>

<code>        28 CON$</code>

<code>        29 C_COBJ#</code>

<code>        20 ICOL$</code>

<code>        44 I_USER1</code>

<code>        15 UNDO$</code>

<code> </code>

<code>SQL> select count(*) from t_test1</code>

<code>  2  where object_name </code>

<code> </code>

<code>  COUNT(*)</code>

<code>----------</code>

<code>     21645</code>

 

出现这种混乱的原因是：Oracle先按物理存储位置（rowid）顺序取出满足rownum条件的记录，即物理位置上的前5条数据，然后在对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。

 

如果需要对非主键字段排序再去前n条数据，我们可以以以下方式实现：

 

<code>SQL> select object_id,object_name</code>

<code>  2  from (select object_id,object_name from t_test1</code>

<code>  3        order by object_name)</code>

<code>  4  where rownum </code>

<code> </code>

<code> OBJECT_ID OBJECT_NAME</code>

<code>---------- ------------------------------</code>

<code>     35489 /1000e8d1_LinkedHashMapValueIt</code>

<code>     35490 /1000e8d1_LinkedHashMapValueIt</code>

<code>     21801 /1005bd30_LnkdConstant</code>

<code>     21802 /1005bd30_LnkdConstant</code>

<code>     17205 /10076b23_OraCustomDatumClosur</code>

 

3.3     排序分页

当对存在重复值的字段排序后再分页输出，我们很容易会陷入到另外一个“陷阱”。

 

请看以下例子，我们希望对T_TEST1的OWNER字段排序后，以每页输出10个结果的方式分页输出：

 

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name from t_test1 order by owner) a</code>

<code>  4  where rownum </code>

<code>  5  where rn >= 1;</code>

<code> </code>

<code>OWNER                          OBJECT_NAME</code>

<code>------------------------------ ------------------------------</code>

<code>AFWOWNER                       AFWADAPTER</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_IDX1</code>

<code>AFWOWNER                       AFWADAPTERFQN_PK</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_PK</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_IDX2</code>

<code>AFWOWNER                       AFWSERVERCODE_PK</code>

<code>AFWOWNER                       <span>AFWSERVER</span></code>

<code>AFWOWNER                       AFWADAPTERLOOKUP_IDX1</code>

<code>AFWOWNER                       <span>AFWADAPTERLOOKUP</span></code>

<code> </code>

<code>10 rows selected.</code>

<code> </code>

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name from t_test1 order by owner) a</code>

<code>  4  where rownum </code>

<code>  5  where rn >= 11;</code>

<code> </code>

<code>OWNER                          OBJECT_NAME</code>

<code>------------------------------ ------------------------------</code>

<code>AFWOWNER                       AFWTOKENSTATUSCODE_PK</code>

<code>AFWOWNER                       AFWTOKENSTATUS</code>

<code>AFWOWNER                       AFWTOKENADMIN_IDX1</code>

<code>AFWOWNER                       AFWTOKENADMINCODE_PK</code>

<code>AFWOWNER                       AFWTOKENADMIN</code>

<code>AFWOWNER                       AFWTOKEN</code>

<code>AFWOWNER                       AFWSERVERCONFIGURATION_PK</code>

<code>AFWOWNER                       AFWSERVERCONFIGURATION</code>

<code>AFWOWNER                       <span>AFWSERVER</span></code>

<code>AFWOWNER                       <span>AFWADAPTERLOOKUP</span></code>

<code> </code>

<code>10 rows selected.</code>

 

仔细比较结果，你会发现“AFWSERVER”、“AFWADAPTERLOOKUP”在两次分页结果中都出现了。但是OBJECT_NAME在每个OWNER中的值是唯一的，说明这个输出结果是错误的，我们又陷入了一个“陷阱”。这是怎么回事呢，请先看下上述语句的查询计划：

 

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name from t_test1 order by owner) a</code>

<code>  4  where rownum </code>

<code>  5  where rn >= 11;</code>

<code> </code>

<code>10 rows selected.</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=205 Card=20 Bytes=94</code>

<code>          0)</code>

<code> </code>

<code>   1    0   VIEW (Cost=205 Card=20 Bytes=940)</code>

<code>   2    1     COUNT (STOPKEY)</code>

<code>   3    2       VIEW (Cost=205 Card=30670 Bytes=1042780)</code>

<code>   4    3         <span>SORT (ORDER BY STOPKEY)</span> (Cost=205 Card=30670 Bytes=858760)</code>

<code>   5    4           TABLE ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=858760)</code>

 

看下这个“陷阱”是怎么形成的。从查询计划上，我们可以注意到，对于子查询

<code>select a.*, rownum as rn from</code>

<code>     (select owner, object_name from t_test1 order by owner) a</code>

<code>where rownum </code>

优化器采用了“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的影响。但这样会使所有数据都做排序：

 

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name, rowid from t_test1 order by owner) a)</code>

<code>  4  where rn </code>

<code>  5  and rn >= 1;</code>

<code> </code>

<code>OWNER                          OBJECT_NAME</code>

<code>------------------------------ ------------------------------</code>

<code>AFWOWNER                       AFWADAPTER</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_IDX2</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_PK</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_IDX1</code>

<code>AFWOWNER                       AFWADAPTERFQN_PK</code>

<code>AFWOWNER                       AFWADAPTERLOOKUP_IDX1</code>

<code>AFWOWNER                       AFWSERVERCODE_PK</code>

<code>AFWOWNER                       AFWSERVERCONFIGURATION_IDX1</code>

<code>AFWOWNER                       AFWTOKENTYPECODE_PK</code>

<code> </code>

<code>10 rows selected.</code>

<code> </code>

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name, rowid from t_test1 order by owner) a)</code>

<code>  4  where rn </code>

<code>  5  and rn >= 11;</code>

<code> </code>

<code>OWNER                          OBJECT_NAME</code>

<code>------------------------------ ------------------------------</code>

<code>AFWOWNER                       AFWTOKENTYPE</code>

<code>AFWOWNER                       AFWTOKENSTATUSCODE_PK</code>

<code>AFWOWNER                       AFWTOKENSTATUS</code>

<code>AFWOWNER                       AFWTOKENADMIN_IDX1</code>

<code>AFWOWNER                       AFWTOKENADMINCODE_PK</code>

<code>AFWOWNER                       AFWTOKENADMIN</code>

<code>AFWOWNER                       AFWTOKEN</code>

<code>AFWOWNER                       AFWSERVERCONFIGURATION_PK</code>

<code>AFWOWNER                       AFWTOKEN_PK</code>

<code>AFWOWNER                       AFWTOKEN_IDX6</code>

<code> </code>

<code>10 rows selected.</code>

<code> </code>

<code>SQL> set autot trace</code>

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name, rowid from t_test1 order by owner) a)</code>

<code>  4  where rn </code>

<code>  5  and rn >= 11;</code>

<code> </code>

<code>10 rows selected.</code>

<code> </code>

<code> </code>

<code>Execution Plan</code>

<code>----------------------------------------------------------</code>

<code>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=237 Card=30670 Bytes=1441490)</code>

<code>   1    0   VIEW (Cost=237 Card=30670 Bytes=1441490)</code>

<code>   2    1     COUNT</code>

<code>   3    2       VIEW (Cost=237 Card=30670 Bytes=1042780)</code>

<code>   4    3         <span>SORT (ORDER BY)</span> (Cost=237 Card=30670 Bytes=1073450)</code>

<code>   5    4           TABLE ACCESS (FULL) OF 'T_TEST1' (Cost=42 Card=30670 Bytes=1073450)</code>

 

2、在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：

 

<code>SQL> select owner, object_name from</code>

<code>  2  (select a.*, rownum as rn from</code>

<code>  3  (select owner, object_name, rowid from t_test1 order by owner, object_id) a</code>

<code>  4  where rownum </code>

<code>  5  where rn >= 1;</code>

<code> </code>

<code>OWNER                          OBJECT_NAME</code>

<code>------------------------------ ------------------------------</code>

<code>AFWOWNER                       AFWADAPTER</code>

<code>AFWOWNER                       AFWADAPTERFQN_PK</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION</code>

<code>AFWOWNER                       AFWADAPTERCONFIGURATION_PK</code>

<code>AFWOWNER                  &nbs</code>