Detailed example of Rownum paging in Oracle, oraclerownum

Detailed example of Rownum paging in Oracle, oraclerownum

In MySQL, we usually use limit to retrieve data sets by page. in Oracle databases, there is no convenient way to achieve paging like limit, therefore, we usually perform paging directly in SQL statements. here we need to use the rownum pseudo column or row_number () function. This article will show the use of the rownum pseudo column and row_number () respectively () analyze functions to complete the specific use of Oracle Data paging operations, and analyze and compare the performance advantages and disadvantages of the two.

I. initialize Test Data

First, I selected 70000 pieces of data from the data dictionary all_objects table. The creation procedure is as follows:
Copy codeThe Code is as follows:
-- To facilitate the verification of result sets and avoid unnecessary sorting, rownum is used to generate an ordered OBJECT_ID column.
SQL> create table my_objects
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.

Ii. Retrieve paging data

To complete the paging, we need to obtain the 10 records in the 59991-60000 table. We use rownum and rown_number () to achieve this.

Copy codeThe Code is as follows:
-- Method 1: rownum pseudo Column
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/585 bb929_DicomRepos24 java class 59991
59992/13 a1874f_DicomRepos25 java class 59992
59993/2322 ccf0_DicomRepos26 java class 59993
59994/6 c82abc6_DicomRepos27 java class 59994
59995/34 be1a57_DicomRepos28 java class 59995
59996/b7ee0c7f_DicomRepos29 java class 59996
59997/bb1d935c_DicomRepos30 java class 59997
59998/deb95b4f_DicomRepos31 java class 59998
59999/9 b5f55c0_DicomRepos32 java class 59999
60000/572 f1657_DicomRepos33 java class 60000

10 rows selected.

-- Method 2: 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/585 bb929_DicomRepos24 java class 59991
59992/13 a1874f_DicomRepos25 java class 59992
59993/2322 ccf0_DicomRepos26 java class 59993
59994/6 c82abc6_DicomRepos27 java class 59994
59995/34 be1a57_DicomRepos28 java class 59995
59996/b7ee0c7f_DicomRepos29 java class 59996
59997/bb1d935c_DicomRepos30 java class 59997
59998/deb95b4f_DicomRepos31 java class 59998
59999/9 b5f55c0_DicomRepos32 java class 59999
60000/572 f1657_DicomRepos33 java class 60000

10 rows selected.

We can see that both methods return the correct result set. In the rownum method, we cannot directly use the rownum pseudo column to execute the "greater than" comparison operation, therefore, we first use rownum in the subquery to obtain the first 60000 pieces of data, and then use the greater than operation in the outer query to remove unnecessary rows. For the row_number () method, the row_number () analysis function sorts data by OBJECT_ID and generates a unique identifier. Then, it obtains the range data through between, which is easy to understand, so is the actual execution like this? Let's analyze the execution details of the two.

Iii. Paging Performance Analysis

First, let's take a look at their execution plan:
Copy codeThe Code is as follows:
SQL> set autotrace traceonly
SQL> set linesize 200

-- Rownum: Execution Plan of pseudo column Paging
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 4k | 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 cballs
0 db block gets
399 consistent gets
0 physical reads
0 redo size
1030 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed

-- Row_number (): Execution Plan by PAGE
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

Bytes ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------
| 0 | select statement | 70000 | limit 1k | 565 (1) | 00:00:07 |
| * 1 | VIEW | 70000 | bytes 1k | 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 |
Bytes ----------------------------------------------------------------------------------------------

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 cballs
0 db block gets
490 consistent gets
0 physical reads
0 redo size
1030 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

From the preceding execution plan, we can see that the rownum method uses the full table scan to obtain the first 60000 rows in the table, and then uses the predicate condition "T ". "NUM"> = 59991 is used to filter out unnecessary rows. Although the row_number () method uses the primary key index to save the window sorting operation generated by the analysis function, however, it first obtains all 70000 rows of data in the table, and then uses the between keyword to filter data rows. Many resources of this operation are consumed by Data Reading, therefore, in the above example, the rownum pseudo-column method achieves better performance. In fact, in most cases, the first rownum method will achieve better performance.

Some people may wonder, since the row_number () method has spent so much resources on reading data, why not let it scan the entire table directly, let's take a look at the situation of full table scan:
Copy codeThe Code is as follows:
-- Directly disable the primary key
SQL> alter table my_objects disable primary key;

Table altered.

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

Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 70000 | limit 1k | 812 (1) | 00:00:10 |
| * 1 | VIEW | 70000 | bytes 1k | 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 |
Bytes -----------------------------------------------------------------------------------------------

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
----------------------------------------------------------
190 recursive cballs
0 db block gets
450 consistent gets
0 physical reads
0 redo size
1030 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed

We can see that the window sort pushed rank method occurs in this full table scan scenario. That is to say, cpu resources are spent sorting object_id again, even though object_id has been sorted in this example, the performance is also inferior to the rownum method.

Therefore, in the process of writing a program, I prefer to use the following rownum Method for Oracle paging operations. The general syntax is as follows:
Copy codeThe Code is 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
192 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
200 SOURCE $ TABLE 200

10 rows selected.

Note:

When writing a program, for ease of understanding, someone may use between in the rownum method to limit the data row. The statement is as follows:
Copy codeThe Code is as follows:
Select t. * from (select rownum num, d. * from my_objects d) t where t. num between 59991 and 60000;

In their opinion, the data rows returned by writing data are consistent with the first rownum method. Oracle will push the predicate between part to the subquery, without affecting the performance, this idea is totally wrong. Let's take a look at its specific execution plan:
Copy codeThe Code is as follows:
SQL> select t. * from (select rownum num, d. * from my_objects d) t where t. num between 59991 and 60000;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1665864874

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
----------------------------------------------------------------------------------
| 0 | select statement | 70000 | limit 1k | 120 (1) | 00:00:02 |
| * 1 | VIEW | 70000 | bytes 1k | 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 cballs
0 db block gets
423 consistent gets
0 physical reads
0 redo size
1030 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

We can see that this query first had a full table scan of 70000 rows, not the expected 60000 rows. The reason is rownum, in a subquery, rownum is used to directly disable the predicate push function in the query transition phase. Therefore, the preceding query can only obtain all the data before applying between to filter out the data. Refer to my [CBO-query conversion inquiry ].

After talking about this, it is actually three SQL statements of Oracle paging. This alone will not be efficient for paging problems with a large amount of data, therefore, some other technologies, such as anti-paradigm design, pre-calculation, or the establishment of appropriate caching mechanisms at the application layer, are also required.