Oracle Paging Query

Universal Oracle has three ways to implement paged queries through the implementation of various rownum, ROWID, or analytic functions. This will be the test table User_info case, a brief implementation of the three main 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;" >--Run 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;" >--run 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; " >--Run 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) The required fields of the query are generally recommended to list the required fields, instead of all detected by the way of select *. This can affect query efficiency.

(3) The use of ROWID paging query efficiency is relatively high, rownum and analysis function respectively. Of course, for tables with very large data volumes. It may be very slow to meet performance requirements only through the above methods. You also need to optimize the full index of SQL or methods.

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

Oracle Paging Query