Summary of SQL syntax for Oracle implementation paging query _oracle

This article summarizes the Oracle implementation of page query SQL syntax, collation for everyone for your reference, details are as follows:

1. No order by sort of the wording. (Highest efficiency)

After testing, this method costs the lowest, only nested one layer, the fastest! Even if the amount of data query is large, and almost unaffected, 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 DD ')
        and To_date (' 20060731 ', ' YYYYMMDD ') and
      rownum <=) table_alias
WHERE Table_alias. Rowno >= 10;

2. There is an order by sort of the wording. (Highest efficiency)

After testing, this method with the scope of the query expanded, the speed will be more and more slow!

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_da Te (' 20060501 ', ' YYYYMMDD ') and
             to_date (' 20060531 ', ' YYYYMMDD ')
          order by Fact_up_time, flight_no) TT
     WHERE RowNum <=) Table_alias
where Table_alias.rowno >= 10;

3. No order by sort of the wording. (We recommend using Method 1 instead)

This method will be slower and faster as the amount of query data expands!

The SQL statement is as follows:

SELECT *
 from (select RowNum as Rowno, t.* from
      k_task T
     where flight_date between (' To_date ', ' 20060501 Ymmdd ')
        and To_date (' 20060731 ', ' YYYYMMDD ') table_alias
WHERE Table_alias. Rowno <= and
  Table_alias. Rowno >=;
Table_alias. Rowno between 100;

4. There is an order by sort of the wording. (We recommend using Method 2 instead)

This method will become more and more slow as the scope of the query expands!

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 and 20;

5. Alternative grammar. (with order by notation)

This grammatical style differs from the traditional SQL syntax, which is not convenient for reading and understanding, and is not recommended for standard and uniform standards. 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_da Te (' 20060501 ', ' YYYYMMDD ') and
            to_date (' 20060531 ', ' YYYYMMDD ')
         order by Fact_up_time, Flight_no) TT
  where rownum <=
  Select * from Partdata where Rowno >= 10;

6. Alternative grammar. (no ORDER by notation)

With Partdata as (
 Select rownum as Rowno, t.* from
  k_task T
  where flight_date between (' To_date ', ' 20060501 Ymmdd ')
     and To_date (' 20060531 ', ' YYYYMMDD ') and
   rownum <=)
  Select * from Partdata where Rowno >= 10; 

I believe that the code described in this article can have a certain reference value.