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.