Statement of Oracle paging Query

Oracle paging query statements make it one of the most common statements. The following describes the usage of Oracle paging query statements. If you are interested in this, take a look.

Oracle paging query statements can be applied in the format given in this article.
Oracle paging Query format:

 
 

  
  
SELECT * FROM  
  
  
(  
  
  
SELECT A.*, ROWNUM RN  
  
  
FROM (SELECT * FROM TABLE_NAME) A  
  
  
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.

The Oracle paging query statement given above has a high efficiency in most cases. The purpose of paging is to control the size of the output result set and return the result as soon as possible. In the preceding paging query statement, this consideration is mainly reflected in the where rownum <= 40 sentence.

There are two methods to select 21st to 40 records. One is that the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, 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. The query statement is as follows:

 
 

  
  
SELECT * FROM  
  
  
(  
  
  
SELECT A.*, ROWNUM RN  
  
  
FROM (SELECT * FROM TABLE_NAME) A  
  
  
)  
  
  
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, however, Oracle cannot push the layer-3 query conditions to the inner layer, which makes no sense even if it is pushed to the inner layer, because the inner 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 query analyzed above is not only a simple query for a single table, but also effective for complex multi-table joint queries or sorting in the innermost query.

Oracle sqlplus statement Editing Command

Oracle user syntax Modification

Oracle Default User Password Problems

Show you the Oracle explicit cursor

Use instances of Oracle stored procedures