Introduction to common Oracle SQL paging Implementation Solutions

In Oracle, there are many ways to implement paging using SQL, but some statements may not be very common and can only be used in some special scenarios. The following describes three general implementation solutions; in the following implementations, ROWNUM is the core keyword. During a query, ROWNUM is a virtual column. The value ranges from 1 to the serial number of the total number of records.

In Oracle, there are many ways to implement paging using SQL, but some statements may not be very common and can only be used in some special scenarios. The following describes three general implementation solutions; in the following implementations, ROWNUM is the core keyword. During a query, ROWNUM is a virtual column. The value ranges from 1 to the serial number of the total number of records.

In Oracle, there are many ways to implement paging using SQL, but some statements may not be very common and can only be used in some special scenarios;
The following describes three general implementation schemes,ROWNUMIs a core keyword. It is a virtual column during query. The value ranges from 1 to the number of records;
First, we will introduce one of the most commonly used implementation methods in our work:

1. SELECT *
2. FROM (select row _. *, ROWNUM _
3. FROM (SELECT *
4. FROM TABLE1
5. WHERETABLE1_ID=XX
6. Order by GMT_CREATE DESC) ROW _
7. WHERE ROWNUM<= 20)
8. Where rownum _>= 10;

The inmost query SELECT statement is the original query statement without paging. you can replace it with any of your Select SQL statements; ROWNUM <= 20 and ROWNUM> = 10 control the range of each page of the paging query.
The purpose of paging is to control the size of the output result set and return the results as soon as possible. The preceding SQL statement has a high efficiency in most cases, mainly reflected in the WHERE ROWNUM <= 20, this controls the maximum number of records in the query process.

The preceding example shows that the maximum value is controlled by ROWNUM <= 20 on the second layer of the query, and the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 20 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. The SQL statement is as follows, that is, the second implementation method to be introduced:

1. SELECT *
2. FROM (select a. *, ROWNUM RN
3. FROM (SELECT *
4. FROM TABLE1
5. WHERETABLE1_ID=XX
6. Order by GMT_CREATE DESC))
7. Where rn between 10 AND 20;

Since Oracle can push the outer query condition to the inner query, it can improve the execution efficiency of the inner query, but it cannot span multiple layers.
For the first query statement, the query condition where rownum <= 20 on the second layer can be pushed to the inner layer query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.
The second query statement, because the query conditions BETWEEN 10 AND 20 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.

The preceding two solutions are completely completed using ROWNUM. The following method combines ROWID and ROWNUM. The SQL statement is as follows:

1. SELECT *
2. FROM (SELECT RID
3. FROM (select r. RID, ROWNUM LINENUM
4. FROM (SELECT ROWID RID
5. FROM TABLE1
6. WHERETABLE1_ID=XX
7. Order by GMT_CREATE DESC) R
8. WHERE ROWNUM<= 20)
9. WHERE LINENUM>= 10) T1,
10. TABLE1 T2
11. WHERET1.RID=T2. ROWID;

From the statement point of view, there are a total of four layers of Select nested queries, the innermost layer is the replacement of the original SQL statement without paging, but his query field only has ROWID, there are no actual table fields to be queried. The actual field values for specific queries are implemented at the outermost layer;
The principle of this method is roughly as follows: first, the ROWID of the 10 actually returned records after the page is queried through ROWNUM, and finally the finally returned field value is queried and returned through ROWID;
Compared with the preceding two implementation methods, the SQL implementation method is more complex and less universal, because the original query statement must be divided into two parts (the query field is at the outermost layer, the table and its query conditions are in the innermost layer );
However, this kind of implementation is advantageous in specific scenarios: for example, we often need to flip pages to the very back, for example, we often need to query 10000-records and later data; this solution may be more efficient than the previous one;
In the preceding scheme, ROWNUM <= 9100 is used to control the data. In this way, 9100 data records need to be queried and then the data between 9000-9100 is obtained, this scheme uses ROWID to retrieve the required 100 pieces of data;

From the perspective of constantly turning back the page, the cost of the first implementation scheme is getting higher and higher, basically linear growth, and the cost of the third scheme is not as fast as that of the former, its growth is only reflected in reading ROWID through the query condition;
Of course, in addition to the above solutions, we can also use the following SQL:

1. SELECT *
2. FROM TABLE1
3. WHERE TABLE1_ID NOT IN
4. (SELECT TABLE1_ID FROM TABLE1 WHERE ROWNUM<= 10)
5. AND ROWNUM<= 10;

1. SELECT *
2. FROM TABLE1
3. WHERE ROWNUM<= 20
4. MINUS
5. SELECT * FROM TABLE1 WHERE ROWNUM<= 10;

..................
Note: When ROWNUM is used as the query condition, it is executed before order by, so be careful;
For example, the following SQL statement cannot be used to query the first 10 records in descending order of TABLE1_ID in table 1:

1. SELECT * FROM TABLE1 WHERE ROWNUM<= 10 order by TABLE1_ID DESC;