Oracle Paging Query

The common Oracle paging query implementation methods are three, respectively, through RowNum, ROWID, or analysis function implementation, this article will be in my Project User_info table of the sub-table as an example, a brief description of the implementation of three kinds of paging:

--Create tablecreate table User_info (  user_id number      () not NULL,  NAME         VARCHAR2 (+),  pet_name     VARCHAR2 (+),  Head_ico     VARCHAR2 (255),  create_date  DATE not NULL)

1. According to rownum pagination

Select tt.* from (select ROWNUM RN, t.* from (select UI. USER_ID, Ui.name, UI. Pet_name, UI. Head_ico, UI. Create_date from the User_info UI ORDER by UI. Create_date DESC) T where ROWNUM < 600010) TT where TT. RN >= 600000;<span style= "color: #ff0000;" >--Execution Time: 1.981 sec </span>

2. According to ROWID pagination

SELECT UI. USER_ID, Ui.name, UI. Pet_name, UI. Head_ico, UI. Create_date from  user_info UI WHERE ROWID in (select Rid from                   (select ROWNUM RN, rids from                           (select ROWID rid
   from user_info                                   ORDER by Create_date DESC)                          where ROWNUM < 600010)                  where RN >= 600000) the order by UI. Create_date desc;<span style= "color: #ff0000;" >--Execution Time: 1.887 sec </span>

3. Paging based on analytic functions

SELECT * FROM  (select UI. USER_ID,               ui.name,               UI. Pet_name,               UI. Head_ico,               UI. Create_date,               row_number () over (the ORDER by UI. Create_date DESC) RK from          user_info UI) T WHERE T.rk < 600010 and   t.rk >= 600000<span style= "COLOR: #ff000 0; " >--Execution Time: 2.886 sec </span>

From the above three SQL we can find:

(1) Regardless of the way to achieve paging, is to use nested subqueries;

(2) Query the required fields are generally recommended to list the required fields, rather than through the method of SELECT * all detected, which will affect the efficiency of the query;

(3) The use of ROWID paging query efficiency is relatively high, rownum and analysis function respectively. Of course, for a table with a large amount of data, it may only be slow to meet performance requirements in this way, and it needs to be perfected by optimizing SQL or indexing.