Detailed Oracle's several paging query statements

Paging Query format:

SELECT　*　FROM
(
SELECT　A.*,　ROWNUM　RN
FROM　(SELECT　*　FROM　TABLE_NAME)　A
WHERE　ROWNUM　<=　40
)
WHERE　RN　>=　21

The most inner query select * FROM table_name represents the original query statement without paging. RowNum <= 40 and RN >= 21 control the scope of each page of the paging query.

The paging query statement given above is highly efficient in most cases. The purpose of paging is to control the size of the output result set and return the result as quickly as possible. In the paging query above, this consideration is mainly reflected in the sentence where RowNum <= 40.

There are two ways to select the 21st to 40th record, one of which is shown in the above example to control the maximum by RowNum <= 40来 in the second layer of the query, which controls the minimum value at the outermost level of the query. The other way is to remove the query's second-level where rownum <= 40 statement, which controls the minimum and maximum paging values at the outermost edge of the query. This is the query statement as follows:

SELECT　*　FROM
(
SELECT　A.*,　ROWNUM　RN
FROM　(SELECT　*　FROM　TABLE_NAME)　A
)
WHERE　RN　BETWEEN　21　AND　40

In contrast to both of these, the first query is much more efficient than the second one in most cases.

This is because in the CBO optimization mode, Oracle can push the outer query criteria into the inner query to improve the efficiency of the inner query execution. For the first query, the second-level query condition where rownum <= 40 can be pushed into an inner query by Oracle, so that once the results of the Oracle query exceed the rownum limit, the query terminates the result.

The second query, because the query conditions between and 40 are present in the third layer of the query, and Oracle cannot push the third-tier query condition to the very inside (even if it doesn't make sense to push to the inner layer, because the most inner query doesn't know what the RN represents). Therefore, for the second query statement, the Oracle is returned to the middle tier with all the data that satisfies the condition, and the middle tier returns to the outermost and all data. The filtering of data is done at the outermost layer, which is obviously much less efficient than the first query.

The query that is analyzed above is not just a simple query for a single table, but is as effective for the most inner query as a complex multiple-table union query or for the most inner-level query that contains the sort.

There is no description of the query that contains the sort, and the next article is illustrated with an example. Here's a brief discussion of multiple-table syndication. For the most common table of magnitude join queries, the CBO may typically use two ways to connect nested loop and hash join (the efficiency of MERGE join is less efficient than hash join, which is not considered by the general CBO). Here, because paging is used, a maximum number of records is specified, and the NESTED loop can stop immediately and return the result to the middle tier when the number of records exceeds the maximum, and the hash join must process all the result sets (the MERGE join also). In most cases, it is more efficient to select the nested loop as a query connection method for paging queries (most of the time when paging queries are the data from the previous pages, the more the number of pages accessed later).

Therefore, if you don't mind using hint in your system, you can rewrite the paging query to:

SELECT　/*+　FIRST_ROWS　*/　*　FROM
(
SELECT　A.*,　ROWNUM　RN
FROM　(SELECT　*　FROM　TABLE_NAME)　A
WHERE　ROWNUM　<=　40
)
WHERE　RN　>=　21