Relationship between rownum and Order by queries in Oracle queries (the first few records of data)

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 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.

This is because Oracle first Retrieves all the data and then sorts the data in the sorting segment (that is, it first has the rownum value before order by sorting ). Assume that you have an SQL statement as follows.

Select emp_no from emp where rownum <10 order by emp_no;

During retrieval, the data is first retrieved, that is, the EMP table data is retrieved, and then the Order by operation is performed. Because ROWNUM data is retrieved before sorting, 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 ROWNUM. As shown below

Select emp_no from (select emp_no from emp order by emp_no) where rownum <10;

In this way, you will find that you can finally retrieve the results you need, but the disadvantage of this write is that it is slower than the previous execution, and the performance may not be very good.