Oracle中使用Rownum分页详细例子
前端之家收集整理的这篇文章主要介绍了
Oracle中使用Rownum分页详细例子,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
在MysqL中,我们通常都使用limit来完成数据集获取的分页操作,而在Oracle数据库中,并没有类似limit一样的方便方法来实现分页,因此我们通常都是直接在sql语句中完成分页,这里就需要借助于rownum伪列或row_number()函数了,本文将分别展示使用rownum伪列和row_number()分析函数来完成Oracle数据分页操作的具体使用方法,并分析和比较两者的性能优劣。
一、初始化测试数据
首先测试数据我选取了数据字典all_objects表中的70000条数据,创建步骤如下:
-- 为了方便验证结果集以及避免不必要的排序,这里我直接使用了rownum来产生了有序的OBJECT_ID列
sql> create table my_objects as
2 select rownum as OBJECT_ID,OBJECT_NAME,OBJECT_TYPE
3 from all_objects where rownum < 70001;
Table created.
-- 对OJBECT_ID列建立主键
sql> alter table my_objects add primary key (object_id);
Table altered.
sql> select count(*) from my_objects;
COUNT(*)
----------
70000
-- 分析该表
sql> exec dbms_stats.gather_table_stats(user,'my_objects',cascade => TRUE);
PL/sql procedure successfully completed.
为了完成分页,我们需要获得该表中的第59991-60000条的10条记录,这个工作我们分别使用rownum和rown_number()来实现
--
方法一,rownum伪列方式
sql> select t.* from (select d.*,rownum num from my_objects d where rownum<=60000) t where t.num>=59991;
OBJECT_ID OBJECT_NAME OBJECT_TYPE NUM
---------- ------------------------------ ------------------- ----------
59991 /585bb929_DicomRepos24 JAVA CLASS 59991
59992 /13a1874f_DicomRepos25 JAVA CLASS 59992
59993 /2322ccf0_DicomRepos26 JAVA CLASS 59993
59994 /6c82abc6_DicomRepos27 JAVA CLASS 59994
59995 /34be1a57_DicomRepos28 JAVA CLASS 59995
59996 /b7ee0c7f_DicomRepos29 JAVA CLASS 59996
59997 /bb1d935c_DicomRepos30 JAVA CLASS 59997
59998 /deb95b4f_DicomRepos31 JAVA CLASS 59998
59999 /9b5f55c0_DicomRepos32 JAVA CLASS 59999
60000 /572f1657_DicomRepos33 JAVA CLASS 60000
10 rows selected.
-- 方法二,row_number分析函数方式
sql> select * from
2 (select t.*,row_number() over (order by t.OBJECT_ID) as num
3 from my_objects t)
4 where num between 59991 and 60000;
OBJECT_ID OBJECT_NAME OBJECT_TYPE NUM
---------- ------------------------------ ------------------- ----------
59991 /585bb929_DicomRepos24 JAVA CLASS 59991
59992 /13a1874f_DicomRepos25 JAVA CLASS 59992
59993 /2322ccf0_DicomRepos26 JAVA CLASS 59993
59994 /6c82abc6_DicomRepos27 JAVA CLASS 59994
59995 /34be1a57_DicomRepos28 JAVA CLASS 59995
59996 /b7ee0c7f_DicomRepos29 JAVA CLASS 59996
59997 /bb1d935c_DicomRepos30 JAVA CLASS 59997
59998 /deb95b4f_DicomRepos31 JAVA CLASS 59998
59999 /9b5f55c0_DicomRepos32 JAVA CLASS 59999
60000 /572f1657_DicomRepos33 JAVA CLASS 60000
10 rows selected.
可以看到这两种方式都返回了正确的结果集;在rownum方法中,由于不可以直接使用rownum伪列执行”大于“比较运算,所以这里是先从子查询中使用rownum来获得前60000条数据,然后在外层查询中使用大于运算去除不需要的行。而对于row_number()方法,row_number()分析函数以OBJECT_ID排序并为其生成了唯一的标识,然后通过between这种便于理解的方式来获取区间数据,那么实际的执行是不是这样的呢?我们来简单分析一下两者的执行细节。
首先还是看一下他们的执行计划:
sql> set autotrace traceonly
sql> set linesize 200
-- rownum伪列分页的执行计划
sql> select t.* from (select d.*,rownum num from my_objects d where rownum<=60000) t where t.num>=59991;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 341064162
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60000 | 3164K| 103 (0)| 00:00:02 |
|* 1 | VIEW | | 60000 | 3164K| 103 (0)| 00:00:02 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| MY_OBJECTS | 60000 | 2226K| 103 (0)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."NUM">=59991)
2 - filter(ROWNUM<=60000)
Statistics
----------------------------------------------------------
163 recursive calls
0 db block gets
399 consistent gets
0 physical reads
0 redo size
1030 bytes sent via sql*Net to client
419 bytes received via sql*Net from client
2 sql*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
-- row_number()分页的执行计划
sql> select * from
2 (select t.*,row_number() over (order by t.OBJECT_ID) as num
3 from my_objects t)
4 where num between 59991 and 60000;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2942654422
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70000 | 3691K| 565 (1)| 00:00:07 |
|* 1 | VIEW | | 70000 | 3691K| 565 (1)| 00:00:07 |
|* 2 | WINDOW NOSORT STOPKEY | | 70000 | 2597K| 565 (1)| 00:00:07 |
| 3 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 70000 | 2597K| 565 (1)| 00:00:07 |
| 4 | INDEX FULL SCAN | SYS_C0011057 | 70000 | | 146 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=59991 AND "NUM"<=60000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=60000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
490 consistent gets
0 physical reads
0 redo size
1030 bytes sent via sql*Net to client
419 bytes received via sql*Net from client
2 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
Table altered.
10 rows selected.
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%cpu)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70000 | 3691K| | 812 (1)| 00:00:10 |
|* 1 | VIEW | | 70000 | 3691K| | 812 (1)| 00:00:10 |
|* 2 | WINDOW SORT PUSHED RANK| | 70000 | 2597K| 3304K| 812 (1)| 00:00:10 |
| 3 | TABLE ACCESS FULL | MY_OBJECTS | 70000 | 2597K| | 120 (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------