Use rownum paging detailed examples in Oracle _oracle

In MySQL, we usually use limit to complete the paging operation of DataSet acquisition, while in the Oracle database, there is no such convenient way to implement paging, so we usually finish paging directly in SQL statement, limit. With the help of the rownum pseudo-column or row_number () function, this article will show you how to use the rownum pseudo-columns and row_number () parsing functions to complete the Oracle data paging operation, and to analyze and compare the performance of both.

First, initialization of test data

First Test data I selected 70,000 data in the All_objects table of the data dictionary to create the following steps:

Copy Code code as follows:

--To facilitate validation of the result set and to avoid unnecessary sorting, I used rownum directly to produce an ordered object_id column.
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.

--Create a primary key for the ojbect_id column
Sql> ALTER TABLE My_objects add primary key (OBJECT_ID);

Table altered.

Sql> Select COUNT (*) from my_objects;

COUNT (*)
----------
70000

--Analyze the table
sql> exec dbms_stats.gather_table_stats (user, ' my_objects ', Cascade => TRUE);

Pl/sql procedure successfully completed.

Second, paging data acquisition

To complete pagination, we need to get 10 records of the 第59991-60000条 in the table, which we use RowNum and Rown_number () to implement

Copy Code code as follows:

--Method One, rownum pseudo column Way
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

Ten rows selected.

--Method Two, Row_number analysis function mode
Sql> SELECT * FROM
2 (select T.*,row_number () over (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

Ten rows selected.

You can see that both of these methods return the correct result set; In the RowNum method, you can use RowNum to get the first 60,000 data from a subquery because you cannot perform a "greater than" comparison operation directly using the rownum. Then use the greater-than operation in the outer query to remove unwanted rows. For the Row_number () method, the Row_number () parse function is sorted by object_id and generates a unique identity, and then the interval data is obtained by between this easy-to-understand way, so is the actual execution? Let's briefly analyze the implementation details of both.

Paging Performance analysis

First, take a look at their implementation plan:

Copy Code code as follows:

Sql> Set Autotrace traceonly
Sql> Set Linesize 200

--rownum pseudo-column paging execution plan
Sql> Select t.* from (select D.*,rownum num from My_objects D where rownum<=60000) T where t.num>=59991;

Ten 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)
Ten rows processed

--row_number () Paging execution plan
Sql> SELECT * FROM
2 (select T.*,row_number () over (t.object_id) as Num
3 from My_objects t)
4 where num between 59991 and 60000;

Ten 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 (the 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)
Ten rows processed

As we can see from the execution plan above, the RowNum method uses a full table scan to get the first 60000 rows in the table and then uses the predicate condition "T". NUM ">=59991 to filter out unwanted rows, while the Row_number () method takes advantage of the primary key index to omit the sort operation of the window created by the analysis function itself, but it first gets all 70000 rows of data in the table. Then use the Between keyword to filter the data rows, the operation of a lot of resources are consumed in the data read, so the above example, the rownum pseudo-column method to achieve better performance, and in fact, in most cases, the first RowNum method will achieve better performance.

Some people may wonder, since the Row_number () method spends so much resources on the data reading, why not just let it scan the whole table, then let's take a look at the case of using a full table scan:

Copy Code code as follows:

--Disable primary key directly
sql> ALTER TABLE my_objects disable primary key;

Table altered.

Sql> SELECT * FROM
2 (select T.*,row_number () over (t.object_id) as Num
3 from My_objects t)
4 where num between 59991 and 60000;

Ten rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:2855691782

-----------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter ("num" >=59991 and "num" <=60000)
2-filter (Row_number () over (the order by "T".) object_id ") <=60000)

Statistics
----------------------------------------------------------
190 Recursive calls
0 db Block gets
450 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
6 Sorts (memory)
0 Sorts (disk)
Ten rows processed

You can see this full table scan scenario with the window sort pushed rank method, which means that CPU resources are spent on the object_id sort, although in this case the object_id is in order and performance is not as rownum.

So in the process of writing a program, I prefer to use the following RowNum method for Oracle paging operations, usually written as follows:

Copy Code code as follows:

--Return 20th page data, 10 rows per page
Sql> Define Pagenum=20
Sql> Define Pagerecord=10
Sql> Select t.* from (select D.*,rownum num from My_objects D
2 where Rownum<=&pagerecord*&pagenum) t
3 where t.num>= (&pagenum-1) *&pagerecord +1;

Old 2:where rownum<=&pagerecord*&pagenum) t
New 2:where rownum<=10*20) t
Old 3:where t.num>= (&pagenum-1) *&pagerecord +1
New 3:where t.num>= (20-1) *10 +1

object_id object_name object_type NUM
---------- ------------------------------ ------------------- ----------
191 Sqlobj$data_pkey INDEX 191
Sqlobj$auxdata TABLE 192
193 I_sqlobj$auxdata_pkey INDEX 193
194 I_sqlobj$auxdata_task INDEX 194
195 Object_usage TABLE 195
196 i_stats_obj# INDEX 196
197 procedure$ TABLE 197
198 procedureinfo$ TABLE 198
199 argument$ TABLE 199
MB source$ TABLE 200

Ten rows selected.

Note:

In order to facilitate understanding when writing a program, someone will use between to qualify the data line in the RowNum method as follows:

Copy Code code as follows:

Select t.* from (select RowNum num, d.* from My_objects D) t where T.num between 59991 and 60000;

In their view, the data rows returned in this way are consistent with the first RowNum method, and Oracle pushes the predicate between part into the subquery, without affecting performance, and the idea is completely wrong, so let's take a look at its specific execution plan:

Copy Code code as follows:

Sql> Select t.* from (select RowNum num, d.* from My_objects D) t where T.num between 59991 and 60000;

Ten rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1665864874

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70000 | 3691k| 120 (1) | 00:00:02 |
|* 1 | VIEW | | 70000 | 3691k| 120 (1) | 00:00:02 |
| 2 | COUNT | | |
| 3 | TABLE ACCESS full| my_objects | 70000 | 2597k| 120 (1) | 00:00:02 |
----------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter ("T".) NUM "<=60000 and" T "." NUM ">=59991)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
423 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)
Ten rows processed

Can be very eye-catching to see this query first occurred 70000 rows of the full table scan, not the expected 60000 rows, the reason or rownum, in the subquery using RowNum directly disable the query conversion phase of predicate forward function, So the above query can only get all the data and then apply between to filter. I can refer to my "cbo-Query Transformation Inquiry".

Said so much, in fact, Oracle's three-page SQL statement, for a very large number of paging problems, simply do not get efficient, so you need to rely on a number of other technologies, such as anti-normal design, pre-calculation or in the application layer to establish the appropriate caching mechanism.