ORACLE Paging Query

Change from: ORACLE Paging Query SQL Syntax - The Most Efficient Paging

Writing without ORDER BY sort. (Highest efficiency)

After testing, this method has the lowest cost, only one layer nested, and the fastest speed! Even if the amount of data queried is large, it is almost unaffected, and the speed is still!

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM emp t
         WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                             AND TO_DATE ('20060731', 'yyyymmdd')
           AND ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

Writing with ORDER BY sort. (Highest efficiency)

After testing, this method will be slower and slower with the enlargement of the query scope.

SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (  SELECT t.*
                    FROM emp t
                   WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                                       AND TO_DATE ('20060731', 'yyyymmdd')
                ORDER BY create_time DESC, emp_no) tt
         WHERE ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

Writing without ORDER BY sort. (Recommended use method 1 instead)

With the expansion of query data, the speed of this method will be slower and slower.

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM k_task t
         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                               AND TO_DATE ('20060731', 'yyyymmdd')) table_alias
 WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;
--TABLE_ALIAS.ROWNO  between 10 and 100;

Writing with ORDER BY sort. (Recommended to use method 2 instead)

With the enlargement of the query scope, the speed of this method will be slower and slower.

SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (  SELECT *
                    FROM k_task t
                   WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                                         AND TO_DATE ('20060531', 'yyyymmdd')
                ORDER BY fact_up_time, flight_no) tt) table_alias
 WHERE table_alias.rowno BETWEEN 10 AND 20;

Alternative grammar. (ORDER BY)

Grammatical style is different from traditional SQL grammar, which is not easy to read and understand. It is not recommended for standardization and unification of standards.

WITH partdata AS
     (
        SELECT ROWNUM AS rowno, tt.*
          FROM (  SELECT *
                    FROM k_task t
                   WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                                         AND TO_DATE ('20060531', 'yyyymmdd')
                ORDER BY fact_up_time, flight_no) tt
         WHERE ROWNUM <= 20)
SELECT *
  FROM partdata
 WHERE rowno >= 10;

Alternative grammar. (No ORDER BY)

WITH partdata AS
     (
        SELECT ROWNUM AS rowno, t.*
          FROM k_task t
         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                               AND TO_DATE ('20060531', 'yyyymmdd')
           AND ROWNUM <= 20)
SELECT *
  FROM partdata
 WHERE rowno >= 10;

yangtingkun analysis:

Change from: yangtingkun's personal space

Oracle's paging query statement can basically be applied in the format given in this paper.

Paging query format:

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT *
                  FROM table_name) a
         WHERE ROWNUM <= 40)
 WHERE rn >= 21

The inner query SELECT * FROM TABLE_NAME indicates the original query statement without page flipping. ROWNUM <= 40 and RN >= 21 control the range of pages for paging queries.

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 results as soon as possible. In the above paging query statement, this consideration is mainly reflected in the sentence WHERE ROWNUM <== 40.

There are two ways to select between 21 and 40 records. One is to control the maximum value through ROWNUM <= 40 at the second level of the query and the minimum value at the outermost level of the query, as shown in the example above. Another way is to remove the WHERE ROWNUM <= 40 statement from the second layer of the query and control the minimum and maximum values of the paging at the outermost layer 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

Comparing these two ways of writing, in most cases, the efficiency of the first query is much higher than that of the second one.

This is because in CBO optimization mode, Oracle can push the outer query conditions into the inner query to improve the execution efficiency of the inner query. For the first query statement, the second query condition WHERE ROWNUM <= 40 can be pushed into the inner query by Oracle, so that once the result of Oracle query exceeds the ROWNUM restriction, the query is terminated and the result is returned.

The second query statement, because the query condition BETWEEN 21 AND 40 exists in the third layer of the query, Oracle can not push the third layer of the query condition to the innermost layer (even to the innermost layer is meaningless, because the innermost query does not know what RN stands for). Therefore, for the second query statement, Oracle's innermost layer returns all the data that meets the criteria to the middle layer, and the middle layer returns all the data to the outermost layer. Data filtering is done at the outermost level, which is obviously much less efficient than the first query.

The queries analyzed above are not only simple queries for a single table, but also effective for the case where the innermost query is a complex multi-table joint query or the innermost query contains sorting.

Queries that contain sorting are not explained here, but will be illustrated in detail in the next article.

The following is a brief discussion of multi-table association.

For the most common join queries of equivalence tables, CBO may generally adopt two join modes: NESTED LOOP and HASH JOIN (MERGE JOIN is less efficient than HASH JOIN, which is not considered by CBO in general). Here, because pagination is used, a maximum number of records returned is specified. NESTED LOOP can stop immediately when the number of records returned exceeds the maximum value and return the results to the middle layer. HASH JOIN must process all the result sets (MERGE JOIN, too). In most cases, NESTED LOOP is more efficient as a query connection method for paging queries (most of the cases when paging queries are querying the data of the first few pages, the lower the probability of accessing the next pages).

Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement as follows:

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT *
                  FROM table_name) a
         WHERE ROWNUM <= 40)
 WHERE rn >= 21