Common SQL Paging statements (Oracle)

Common Oracle Query statements

1. No order by ordering. (Highest efficiency)

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

The SQL statements are 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 <=) Table_alias
WHERE Table_alias. ROWNO >= 10;

2. There is an order by ordering the notation. (Highest efficiency)

After testing, this method will become more and more slow as the query scope expands!

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 <=) table_alias
where Table_alias.rowno >= 10;

3. No order by ordering. (It is recommended to use Method 1 instead)
This method will be more and more slow as the amount of query data expands!

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

4. There is an order by ordering the notation. (It is recommended to use Method 2 instead)

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

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 syntax. (with order by notation)

This grammar style differs from the traditional SQL syntax and is not easy to read and understand, and is not recommended for standard and uniform standards. The code is posted here for your reference.

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 syntax. (no ORDER by notation)

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;

Common SQL Paging statements (Oracle)