Eight, Oracle Paging

Source: Internet
Author: User

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;
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.