Oracle paging technology details

 
Paging query statement:
Select * from (
Select A. *, rownum rn from (
Select * from table table_name)
Where rownum <= 40) Where rn> = 20;
Select * from table_name indicates the original query statement that does not flip pages. Rownu <= 40 and rn> = 21 control the range of each page of the paging query.
The purpose of paging is to control the size and size of the output result set, return the results as soon as possible, and control the minimum value at the outermost layer of the query. The other method is to remove the where ronum <= 40 statement at the second layer of the query, and control the maximum value and maximum value of the page at the outermost layer of the query. The query statement is as follows:
Select * from (select A. *, rownum ru from (select * from table_name) A) where ru between 21 and 40
By comparing the two methods, the efficiency of the first query is much higher than that of the second query in the case of a large number.
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 results will be terminated and returned.
The second query statement, because the query conditions between 21 and 40 exist and query the third layer, oracle cannot push the layer-3 query conditions to the innermost layer (even if it is pushed to the innermost layer, it makes no sense because the innermost layer query does not know what rn represents ). Therefore, for the second query statement, the innermost layer of Oracle returns all data that meets the conditions to the middle layer, and the middle layer returns all data to the outermost layer. The data is completed at the outermost layer. Obviously, this efficiency is much lower than the first query!
The above analysis query is not only a simple query of a single table, but also the case where the inmost query contains an arrangement after the complex and multi-table joint query.



From song lixing's column