--1: No ORDER BY sorting method. (most efficient) ­
--(After testing, this method has the lowest cost, only one level of nesting, and the fastest speed! Even if the amount of data queried is large, it is almost unaffected and the speed is still the same!) ­
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 an ORDER BY sorting method. (most efficient) ­
--(After testing, this method will become slower and slower as the query range 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 <= 20) TABLE_ALIAS ­
where TABLE_ALIAS.rowno >= 10; ­
­
--3: No ORDER BY sorting method. (It is recommended to use method 1 instead) ­
--(This method will become slower and slower 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 10 and 100; ­
­
--4: There is an ORDER BY sorting method. (It is recommended to use method 2 instead) ­
--(This method will become slower and slower as the query range 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 10 AND 20; ­
­
­
--5 alternative syntax. (With ORDER BY writing method) ­
--(The syntax style is different from the traditional SQL syntax, which is inconvenient to read and understand. It is a standard and unified standard and is not recommended.) ­
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 writing method) ­
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; ­
-