Eight, Oracle Paging

There are three ways Oracle's paging

Method one according to rowID to divide

SELECT *
  From EMP
WHERE ROWID in
       (SELECT RID
          From (SELECT ROWNUM RN, RID
                  From (SELECT ROWID rids, EMPNO from EMP ORDER by EMPNO DESC)
                 WHERE ROWNUM <= ((currentPage-1) * pageSize + pageSize)--show several per page
         WHERE RN > ((currentPage-1) * pageSize)--current page
ORDER by EMPNO DESC;

eg
--5 = (currentPage-1) * pageSize + pageSize   show a few per page
--0 = (currentPage-1) * pageSize              Current page
SELECT *
  From EMP
WHERE ROWID in
       (SELECT RID
          From (SELECT ROWNUM RN, RID
                  From (SELECT ROWID rids, EMPNO from EMP ORDER by EMPNO DESC)
                 WHERE ROWNUM <= ((1-1) * 5 + 5))--Show several per page
         WHERE RN > ((1-1) * 5)--current page
ORDER by EMPNO DESC;

Method Two according to the analysis function to divide

SELECT *
From (SELECT t.*, Row_number () Up (ORDER by Empno DESC) RK from EMP T)
WHERE RK <= ((currentPage-1) * pageSize + pageSize)--show several per page
and RK > ((currentPage-1) * pageSize); --Current page

eg
--5 = (currentPage-1) * pageSize + pageSize   show a few per page
--0 = (currentPage-1) * pageSize              Current page
SELECT *
From (SELECT t.*, Row_number () Up (ORDER by Empno DESC) RK from EMP T)
WHERE RK <= 5
and RK > 0;

Method three press RowNum to divide

SELECT *
  From (SELECT t.*, ROWNUM RN
          From (SELECT * from EMP ORDER by EMPNO DESC) T
         WHERE ROWNUM <= ((currentPage-1) * pageSize + pageSize)--show several per page
WHERE RN > ((currentPage-1) * pageSize); --Current page
           
eg
--5 = (currentPage-1) * pageSize + pageSize   show a few per page
--0 = (currentPage-1) * pageSize              Current page
SELECT *
  From (SELECT t.*, ROWNUM RN
          From (SELECT * from EMP ORDER by EMPNO DESC) T
         WHERE ROWNUM <= 5)
WHERE RN > 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 * from EMP;
2. Display RowNum, assigned by Oracle

3, first find out 1-10 records
Correct: Select E.*, ROWNUM rn from (SELECT * from EMP) e WHERE rownum<=10;
Wrong: Select e.*, ROWNUM rn from (SELECT * from EMP) e WHERE rn<=10;
4, and then found 6-10 records
SELECT * FROM (select e.*, ROWNUM rn from (SELECT * from EMP) e where rownum<=10) where rn>=6;

Eight, Oracle Paging