How to Implement paging query for Oracle databases

Source: Internet
Author: User

The data in the specified region of the Oracle database cannot be directly limit x or y as mysql does. We need to use the inner-layer query method to implement paging query.

Paging Query format:

The Code is as follows: Copy code

SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn> = 21

SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.


There are two methods for selecting 21st to 40 records. One is that ROWNUM <= 40 is used to control the maximum value in the second layer of the query, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. Here is the query statement:

The Code is as follows: Copy code

SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
)
Where rn between 21 AND 40

In most cases, the efficiency of the first query is much higher than that of the second query.

This is because in the CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the first query statement, the SQL query condition WHERE ROWNUM <= 40 can be pushed to the inner query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.

The second query statement, because the query conditions BETWEEN 21 AND 40 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.

The following is a list of related query performance comparison data.


1. Minute by ROWID

The Code is as follows: Copy code

Select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from (select rowid rid, cid from

T_xiaoxi order by cid desc) where rownum <10000) where rn> 9980) order by cid desc;

Execution time: 0.03 seconds


2. Score by Analysis Function

The Code is as follows: Copy code

Select * from (select t. *, row_number () over (order by cid desc) rk from t_xiaoxi t) where rk <10000 and rk> 9980;

Execution time: 1.01 seconds


3. Divide by ROWNUM

The Code is as follows: Copy code

Select * from (select t. *, rownum rn from (select * from t_xiaoxi order by cid desc) t where rownum <10000) where

Rn> 9980; execution time: 0.1 seconds

Here, t_xiaoxi is the table name, cid is the table's key field, and the records 9981-9999 are sorted by CID in descending order. The t_xiaoxi table has more than 70000 records.

Personal perception 1 is the best, followed by 3, and 2 is the worst.

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.