Brief Analysis of Oracle Rownum

During application development, it is common to sort data according to certain rules and then retrieve the first few data records. In Oracle, order by is commonly used to obtain data smaller than Oracle Rownum. However, if you are not familiar with Oracle, you may find that the value retrieved from the SQL statement you wrote is incorrect. Why.

Oracle first Retrieves all the data and sorts the data in the sorting segment. Assume that you have an SQL statement as follows:

 
 

  
  
SELECTEMP_NOFROMEMPWHEREROWNUM < 10ORDER BYEMP_NO 
 
 

During retrieval, the data is first retrieved, that is, the EMP table data is retrieved, and then the Order by operation is performed. Since Oracle Rownum data is retrieved before sorting, Oracle Rownum cannot be used to obtain the first 10 operations after sorting. So how should we operate it. In fact, it is very easy to use subqueries, first sort and then get Oracle Rownum. As follows:

 
 

  
  
SELECTEMP_NOFROM(  
  
  
SELECTEMP_NOFROM EMPORDER BYEMP_NO  
  
  
)  
  
  
WHERE  
  
  
ROWNUM < 10 
 
 

1. Abstract Oracle Check Constraints
2. Introduction to Oracle index creation policies
3. Oracle Where clause research conclusion
4. Oracle UNION ALL
5. Overview of Oracle Unique constraints