Paging Queries for Oracle

--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! )

SELECT *   from(SELECTROWNUM asRowno, T.*           fromEMP TWHEREHire_datebetweenTo_date ('20060501','YYYYMMDD')                              andTo_date ('20060731','YYYYMMDD')            andROWNUM<=  -) Table_aliasWHERETable_alias.rowno>= Ten;

--2: There is an order by ordering the notation. (Highest efficiency)
--(after testing, this method as the query scope expands, the speed will be more and more slow Oh!) )

SELECT *   from(SELECTTt.*, ROWNUM asRowno from(SELECTT.*                     fromEMP TWHEREHire_datebetweenTo_date ('20060501','YYYYMMDD')                                        andTo_date ('20060731','YYYYMMDD')                ORDER  byCreate_timeDESC, emp_no) TTWHEREROWNUM<=  -) Table_aliasWHERETable_alias.rowno>= Ten;

--3: No ORDER by ordering. (It is recommended to use Method 1 instead)
--(this method with the query data volume expansion, the speed will be more and more slow Oh!) )

SELECT *   from(SELECTROWNUM asRowno, T.*           fromK_task TWHEREFlight_datebetweenTo_date ('20060501','YYYYMMDD')                                andTo_date ('20060731','YYYYMMDD')) Table_aliasWHERETable_alias.rowno<=  -  andTable_alias.rowno>= Ten;

--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 be more and more slow as the scope of the query expands!) )

SELECT *   from(SELECTTt.*, ROWNUM asRowno from(SELECT *                   fromK_task TWHEREFlight_datebetweenTo_date ('20060501','YYYYMMDD')                                          andTo_date ('20060531','YYYYMMDD')                ORDER  byFact_up_time, Flight_no) TT) Table_aliasWHERETable_alias.rownobetween Ten  and  -;

--5 Alternative Grammar. (with order by notation)
--(the grammatical style differs from the traditional SQL syntax, it is inconvenient to read and understand, and is not recommended for standard and uniform standards.) )

 withPartdata as     (        SELECTROWNUM asRowno, TT.*           from(SELECT *                     fromK_task TWHEREFlight_datebetweenTo_date ('20060501','YYYYMMDD')                                          andTo_date ('20060531','YYYYMMDD')               ORDER  byFact_up_time, Flight_no) TTWHEREROWNUM<=  -)SELECT *   fromPartdataWHERERowno>= Ten;

--6 Alternative Grammar. (no ORDER by notation)

 withPartdata as     (        SELECTROWNUM asRowno, T.*           fromK_task TWHEREFlight_datebetweenTo_date ('20060501','YYYYMMDD')                               andTo_date ('20060531','YYYYMMDD')            andROWNUM<=  -)SELECT *   fromPartdataWHERERowno>= Ten;

Paging Queries for Oracle