1. rownum定义
Oracle官方手册12.2关于rownum描述如下：
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
意思就是从一个查询块中获取每一行数据时给一个序号，获取的第一行数据为序号1，第二行为序号2，所以这里要注意是取数的顺序值，那么同一个查询就可能因为执行计划发生变化、表重建等原因导致rownum发生变化。
注意这里作用的范围为查询块：rownum为哪一层就是哪一层取数的值，这个逻辑会导致将该层独立出来，外部条件都跟该层无关，所以也就产生了外部谓词无法推入的问题。
2. rownum常用场景
2.1 生成序列
如下生成1-10的序列 SQL> select rownum as id from dual connect by rownum<=10; ID ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. 加sysdate生成最近10天的日期序列 SQL> select sysdate+rownum as id from dual connect by rownum<=10; ID ------------------- 2022-07-23 06:36:00 2022-07-24 06:36:00 2022-07-25 06:36:00 2022-07-26 06:36:00 2022-07-27 06:36:00 2022-07-28 06:36:00 2022-07-29 06:36:00 2022-07-30 06:36:00 2022-07-31 06:36:00 2022-08-01 06:36:00 10 rows selected.复制
2.2 分页查询
正确的分页查询是有3层嵌套的,而且最里面一层是有order by的，没有order by出来的结果是可能发生变化的。
select * from (select rownum as rwnum, object_id, object_name, object_type from (select object_id, object_name, object_type from t1 order by object_id) where rownum <= :endnum) where rwnum >= :startnum; SQL> select * from (select rownum as rwnum,object_id,object_name,object_type from (select object_id,object_name,object_type from t1 order by object_id) where rownum<=10) 2 where rwnum>=6; RWNUM OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 6 7 I_TS# INDEX 7 8 C_FILE#_BLOCK# CLUSTER 8 9 I_FILE#_BLOCK# INDEX 9 10 C_USER# CLUSTER 10 11 I_USER# INDEX SQL> select * from (select rownum as rwnum,object_id,object_name,object_type from (select object_id,object_name,object_type from t1 order by object_id) where rownum<=15) 2 where rwnum>=11; RWNUM OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 11 12 FET$ TABLE 12 13 UET$ TABLE 13 14 SEG$ TABLE 14 15 UNDO$ TABLE 15 16 TS$ TABLE复制
3. rownum 注意事项
3.1 rownum永远从1开始
rownum是从当前查询块的按取数顺序1开始的，第一行永远是1，先有1才可能有2、3…，想直接通过rownum获取大于1的值是不可能的。
SQL> select rownum,object_id,object_name from test; ROWNUM OBJECT_ID OBJECT_NAME ---------- ---------- ------------------------------ 1 16 TS$ 2 20 ICOL$ 3 8 C_FILE#_BLOCK# 4 37 I_OBJ2 5 22 USER$ 6 33 I_TAB1 7 40 I_OBJ5 8 31 CDEF$ 9 41 I_IND1 10 3 I_OBJ# 11 6 C_TS# SQL> select rownum,object_id,object_name from test where rownum<=1; ROWNUM OBJECT_ID OBJECT_NAME ---------- ---------- ------------------------------ 1 16 TS$ --rownum大于1时返回结果为空 SQL> select rownum,object_id,object_name from test where rownum>1; no rows selected SQL>复制
3.2 rownum并不固定
没有通过order by固定顺序的结果是可能发生变化的。如下例子：相同的查询只是因为执行计划不同而结果不同。
select /*+ index(t ind_objname_test) */ rownum,object_id,object_name from test t where object_name like 'A%' and rownum<=10; select /*+ full(t) */ rownum,object_id,object_name from test t where object_name like 'A%' and rownum<=10; SQL> select /*+ index(t ind_objname_test) */ rownum,object_id,object_name from test t where object_name like 'A%' and rownum<=10; ROWNUM OBJECT_ID OBJECT_NAME ---------- ---------- ------------------------------ 1 77698 A 2 78905 A 3 77760 AB 4 22019 ABSPATH 5 22022 ABSPATH 6 84 ACCESS$ 7 62960 ACCESSION_NUMBER_ORD_IMG_T 8 10523 ACL$_OBJ 9 624 ACLMV$ 10 8097 ACLMV$_BASE_VIEW 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 912768745 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 390 | 9 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 11 | 429 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_OBJNAME_TEST | 3077 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - access("OBJECT_NAME" LIKE 'A%') filter("OBJECT_NAME" LIKE 'A%') SQL> select /*+ full(t) */ rownum,object_id,object_name from test t where object_name like 'A%' and rownum<=10; ROWNUM OBJECT_ID OBJECT_NAME ---------- ---------- ------------------------------ 1 84 ACCESS$ 2 173 ADMINAUTH$ 3 244 APP$SYSTEM$SEQ 4 297 ATEMPTAB$ 5 298 ATEMPIND$ 6 369 ARGUMENT$ 7 399 ASSEMBLY$ 8 529 AUDSES$ 9 530 AUDIT$ 10 532 AUD_POLICY$ 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3931117773 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 390 | 3 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | TABLE ACCESS FULL| TEST | 11 | 429 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 2 - filter("OBJECT_NAME" LIKE 'A%')复制
3.3 子查询使用rownum
子查询中使用rownum导致外部谓词无法推进，执行计划为全表扫描
SQL> select * from (select rownum,object_id,object_name,object_type from test) where object_id=10; ROWNUM OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 33 10 C_USER# CLUSTER Execution Plan ---------------------------------------------------------- Plan hash value: 1984214786 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 75009 | 7691K| 403 (1)| 00:00:01 | |* 1 | VIEW | | 75009 | 7691K| 403 (1)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| TEST | 75009 | 3589K| 403 (1)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=10) 将rownum放到子查询外面，外部谓词推入到子查询，执行计划变成了索引扫描，但这里要注意rownum的值变成了1，因为对于外层查询取的数就是1。 SQL> select rownum,object_id,object_name,object_type from (select object_id,object_name,object_type from test) where object_id=10; ROWNUM OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 1 10 C_USER# CLUSTER Execution Plan ---------------------------------------------------------- Plan hash value: 2605011323 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | COUNT | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 49 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_OBJID_TEST | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=10)复制
3.4 视图使用rownum
同子查询一样.
SQL> create or replace view v_t1 as select rownum as id,object_id,object_name,object_type from test; View created. SQL> create or replace view v_t2 as select object_id,object_name,object_type from test; View created. SQL> select * from v_t1 where object_id=10; ID OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 33 10 C_USER# CLUSTER Execution Plan ---------------------------------------------------------- Plan hash value: 3309629652 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 75009 | 7691K| 403 (1)| 00:00:01 | |* 1 | VIEW | V_T1 | 75009 | 7691K| 403 (1)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| TEST | 75009 | 3589K| 403 (1)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=10) SQL> select rownum as id,t.* from v_t2 t where object_id=10; ID OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- ------------------------------ ------------------------------ 1 10 C_USER# CLUSTER Execution Plan ---------------------------------------------------------- Plan hash value: 2605011323 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | COUNT | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 49 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_OBJID_TEST | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=10)复制
3.5 排序使用rownum
rownum和order by在同一个查询块时，rownum是先执行的，order by是后执行的，也就是说order by只是先按取数顺序取了rownum的数后再进行排序，排序不是全局的。
SQL> select object_id,object_name,object_type from test where rownum<=10 order by object_id; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 3 I_OBJ# INDEX 8 C_FILE#_BLOCK# CLUSTER 16 TS$ TABLE 20 ICOL$ TABLE 22 USER$ TABLE 31 CDEF$ TABLE 33 I_TAB1 INDEX 37 I_OBJ2 INDEX 40 I_OBJ5 INDEX 41 I_IND1 INDEX 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3047351041 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 490 | 3 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 10 | 490 | 3 (34)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL| TEST | 10 | 490 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- 去掉order by取的值和上面是一样的，只是顺序不一样。 SQL> select object_id,object_name,object_type from test where rownum<=10; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 16 TS$ TABLE 20 ICOL$ TABLE 8 C_FILE#_BLOCK# CLUSTER 37 I_OBJ2 INDEX 22 USER$ TABLE 33 I_TAB1 INDEX 40 I_OBJ5 INDEX 31 CDEF$ TABLE 41 I_IND1 INDEX 3 I_OBJ# INDEX 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3931117773 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 490 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| TEST | 10 | 490 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 但很可能你想要的结果是这样的 SQL> select * from (select object_id,object_name,object_type from test order by object_id) where rownum<=10; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------------------ 2 C_OBJ# CLUSTER 3 I_OBJ# INDEX 4 TAB$ TABLE 5 CLU$ TABLE 6 C_TS# CLUSTER 7 I_TS# INDEX 8 C_FILE#_BLOCK# CLUSTER 9 I_FILE#_BLOCK# INDEX 10 C_USER# CLUSTER 11 I_USER# INDEX 10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1607412806 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 920 | | 1314 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 75009 | 6739K| | 1314 (1)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY| | 75009 | 3589K| 4424K| 1314 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | TEST | 75009 | 3589K| | 403 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - filter(ROWNUM<=10)复制
最后修改时间：2022-07-23 16:14:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
