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>
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
MySQL如何处理数据复制？Apr 28, 2025 am 12:25 AMMySQL通过异步、半同步和组复制三种模式处理数据复制。1）异步复制性能高但可能丢失数据。2）半同步复制提高数据安全性但增加延迟。3）组复制支持多主复制和故障转移，适用于高可用性需求。
您如何使用解释性语句分析查询性能？Apr 28, 2025 am 12:24 AMEXPLAIN语句可用于分析和提升SQL查询性能。1.执行EXPLAIN语句查看查询计划。2.分析输出结果，关注访问类型、索引使用情况和JOIN顺序。3.根据分析结果，创建或调整索引，优化JOIN操作，避免全表扫描，以提升查询效率。
您如何备份并还原MySQL数据库？Apr 28, 2025 am 12:23 AM使用mysqldump进行逻辑备份和MySQLEnterpriseBackup进行热备份是备份MySQL数据库的有效方法。1.使用mysqldump备份数据库：mysqldump-uroot-pmydatabase>mydatabase_backup.sql。2.使用MySQLEnterpriseBackup进行热备份：mysqlbackup--user=root--password=password--backup-dir=/path/to/backupbackup。恢复时，使用相应的命
MySQL中慢速查询的常见原因是什么？Apr 28, 2025 am 12:18 AMMySQL慢查询的主要原因包括索引缺失或不当使用、查询复杂度、数据量过大和硬件资源不足。优化建议包括：1.创建合适的索引；2.优化查询语句；3.使用分表分区技术；4.适当升级硬件。
MySQL中有什么看法？Apr 28, 2025 am 12:04 AMMySQL视图是基于SQL查询结果的虚拟表，不存储数据。1)视图简化复杂查询，2)增强数据安全性，3)维护数据一致性。视图是数据库中的存储查询，可像表一样使用，但数据动态生成。
MySQL和其他SQL方言之间的语法有什么区别？Apr 27, 2025 am 12:26 AMmysqldiffersfromothersqldialectsinsyntaxforlimit，自动启动，弦乐范围，子征服和表面上分析。1）MySqluessLipslimit，whilesqlserverusestopopandoraclesrontersrontsrontsrontsronnum.2）
什么是mysql分区？Apr 27, 2025 am 12:23 AMMySQL分区能提升性能和简化维护。1）通过按特定标准（如日期范围）将大表分成小块，2）物理上将数据分成独立文件，3）查询时MySQL可专注于相关分区，4）查询优化器可跳过不相关分区，5）选择合适的分区策略并定期维护是关键。
您如何在MySQL中授予和撤销特权？Apr 27, 2025 am 12:21 AM在MySQL中，如何授予和撤销权限？1.使用GRANT语句授予权限，如GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'；2.使用REVOKE语句撤销权限，如REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host'，确保及时沟通权限变更。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸！
热门文章
刺客信条阴影：贝壳谜语解决方案
1 个月前ByDDD
Windows 11 KB5054979中的新功能以及如何解决更新问题
3 周前ByDDD
在哪里可以找到原子中的起重机控制钥匙卡
1 个月前ByDDD
如何修复KB5055523无法在Windows 11中安装？
2 周前ByDDD
Inzoi：如何申请学校和大学
3 周前ByDDD
热工具
记事本++7.3.1
好用且免费的代码编辑器
DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中
Dreamweaver Mac版
视觉化网页开发工具
SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合，这些列表在安全评估过程中经常使用，都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表，帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上，他就可以访问到所需的每种类型的列表。
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
