Eight, Oracle Paging

There are three ways Oracle's paging

Method one according to rowID to divide

SELECT *   fromEMPWHEREROWIDinch       (SELECTRID from(SELECTROWNUM RN, RID from(SELECTROWID RIDs, EMPNO fromEmpORDER  byEMPNODESC)                 WHEREROWNUM<=((currentpage-1)*PageSize+PageSize))--each page shows several         WHERERn>((currentpage-1)*PageSize))--Current Page ORDER  byEMPNODESC; eg,--5 = (currentPage-1) * pageSize + pageSize show a few per page--0 = (currentPage-1) * pageSize Current pageSELECT *   fromEMPWHEREROWIDinch       (SELECTRID from(SELECTROWNUM RN, RID from(SELECTROWID RIDs, EMPNO fromEmpORDER  byEMPNODESC)                 WHEREROWNUM<=( (1-1)* 5 + 5))--each page shows several         WHERERn>((1-1)* 5) )--Current Page ORDER  byEMPNODESC

Method Two according to the analysis function to divide

SELECT * from(SELECTT.*, Row_number () Over(ORDER  byEmpnoDESC) RK fromemp T)WHERERk<=((currentpage-1)*PageSize+PageSize)--each page shows several andRk>((currentpage-1)*PageSize);--Current Pageeg,--5 = (currentPage-1) * pageSize + pageSize show a few per page--0 = (currentPage-1) * pageSize Current pageSELECT * from(SELECTT.*, Row_number () Over(ORDER  byEmpnoDESC) RK fromemp T)WHERERk<= 5 andRk> 0;

Method three press RowNum to divide

SELECT *   from(SELECTT.*, ROWNUM RN from(SELECT *  fromEmpORDER  byEMPNODESC) TWHEREROWNUM<=((currentpage-1)*PageSize+PageSize))--each page shows several WHERERn>((currentpage-1)*PageSize);--Current Pageeg,--5 = (currentPage-1) * pageSize + pageSize show a few per page--0 = (currentPage-1) * pageSize Current pageSELECT *   from(SELECTT.*, ROWNUM RN from(SELECT *  fromEmpORDER  byEMPNODESC) TWHEREROWNUM<= 5) WHERERn> 0;

Where EMP is the table name, Empno is the primary key ID of the table, gets the 第1-5条 record sorted in descending order of empno, the EMP table has more than 70,000 records.
Personal feeling method One of the best efficiency, method three times, method two the worst.

The following method Sunline analyzes how Oracle through RowNum paging

1,SELECT *  fromEMP;2, display rownum, assigned by OracleSELECTE.*, ROWNUM RN from(SELECT *  fromEMP) e;--RN equals the ID number of the line assigned by Oracle3, first find out 1-10 records are correct:SELECTE.*, ROWNUM RN from(SELECT *  fromEMP) EWHEREROWNUM<=Tenor wrong:SELECTE.*, ROWNUM RN from(SELECT *  fromEMP) EWHERERn<=Ten;4, and then find out 6-10 RecordsSELECT *  from(SELECTE.*, ROWNUM RN from(SELECT *  fromEMP) EWHEREROWNUM<=Ten)WHERERn>=6;