Oracle paging query SQL syntax

Oracle paging query SQL syntax classification: Oracle column 4850 people read comments (1) collect reports

-- 1: no order by statement. (Most efficient)
-- (After testing, this method has the lowest cost. Only one layer is nested, and the speed is the fastest! Even if the queried data volume is large, it is almost unaffected and the speed is still high !)
Select *
From (select rownum as rowno, T .*
From k_task t
Where flight_date between to_date ('20140901', 'yyyymmdd') and
To_date ('20140901', 'yyyymmdd ')
And rownum <= 20) table_alias
Where table_alias.rowno> = 10;

-- 2: Order by sorting. (Most efficient)
-- (After testing, this method slows down 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 ('20140901', 'yyyymmdd') and
To_date ('20140901', 'yyyymmdd ')
Order by fact_up_time, flight_no) TT
Where rownum <= 20) table_alias
Where table_alias.rowno> = 10;

-- 3: no order by statement. (Method 1 is recommended)
-- (This method slows down as the query data size expands !)
Select *
From (select rownum as rowno, T .*
From k_task t
Where flight_date between to_date ('20140901', 'yyyymmdd') and
To_date ('20140901', '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 Statement (method 2 is recommended)
-- (This method slows down as the query range expands !)
Select *
From (select TT. *, rownum as rowno
From (select *
From k_task t
Where flight_date between to_date ('20140901', 'yyyymmdd') and
To_date ('20140901', 'yyyymmdd ')
Order by fact_up_time, flight_no) TT) table_alias
Where table_alias.rowno between 10 and 20;

-- 5 alternative syntax. (Order by Statement)
-- (The syntax style is different from the traditional SQL syntax, which is not easy to read and understand. It is not recommended for standardization and unified standards .)
With partdata (
Select rownum as rowno, TT. * from (select *
From k_task t
Where flight_date between to_date ('20140901', 'yyyymmdd') and
To_date ('20140901', 'yyyymmdd ')
Order by fact_up_time, flight_no) TT
Where rownum <= 20)
Select * From partdata where rowno> = 10;

-- 6 alternative syntax. (No order by Statement)
With partdata (
Select rownum as rowno, T .*
From k_task t
Where flight_date between to_date ('20140901', 'yyyymmdd') and
To_date ('20140901', 'yyyymmdd ')
And rownum <= 20)
Select * From partdata where rowno> = 10;