Oracle Syntax Summary for Paging Query

This article summarizes the SQL syntax of Oracle to realize paging query, and collates it for everyone's reference. The details are as follows:

1. There is no writing of ORDER BY sorting. (Most efficient)

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

The sql statement is as follows:

SELECT *
 FROM (Select ROWNUM AS ROWNO, T.*
      from k_task T
     where Flight_date between to_date('20060501', 'yyyymmdd') and
        to_date('20060731', 'yyyymmdd')
      AND ROWNUM <= 20) TABLE_ALIAS
WHERE TABLE_ALIAS.ROWNO >= 10;

2. There is the writing of ORDER BY sorting. (Most efficient)

After testing, with the expansion of query scope, the speed of this method will become slower and slower!

The sql statement is as follows:

SELECT *
 FROM (SELECT TT.*, ROWNUM AS ROWNO
      FROM (Select t.*
          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) TABLE_ALIAS
where TABLE_ALIAS.rowno >= 10;

3. There is no writing of ORDER BY sorting. (Method 1 is recommended instead)

With the expansion of query data, the speed of this method will become slower and slower!

The sql statement is as follows:

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;

4. There is the writing of ORDER BY sort. (It is recommended to use method 2 instead)

As the query scope expands, the speed of this method will become slower and slower!

The sql statement is as follows:

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;

5. Alternative grammar. (Written in ORDER BY)

This grammar style is different from the traditional SQL grammar, which is inconvenient to read and understand. It is a standard and unified 1 standard, so it is not recommended. The code is posted here for your reference.

The sql statement is as follows:

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;

6. Alternative grammar. (No ORDER BY writing)

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; 

I believe that the code described in this paper can have a certain reference value for everyone.