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.