Oracle Query Top 10 records and paging query (5th to 10th record)

If you want to know the actual application of the first 10 records of Oracle query, you can click on the following article on the actual relevant operation of the correct use, there is a more perfect understanding, I hope you browse the following article will be the following is the text of the detailed content of the introduction.

How does Oracle Query the TOP10 records in a table?

select *  
from test  
where rownum <=10  

Here is an introduction to RowNum

Use of RowNum and Row_number () over ()

RowNum is a pseudo-column provided by Oracle starting from 8, which is to number the results of SQL, always starting from 1, and the common use is to page out the output.

Like what

SELECT *  
FROM torderdetail a  
WHERE ROWNUM <= 10  

This statement is the output of the first 10 records, where the use of similar to the SQL Sever top, but rownum for the specified number range of the output should be said to be more powerful

SELECT *  
FROM (SELECT a.*, ROWNUM rn  
FROM torderdetail a)  
WHERE rn >= 10 AND rn <= 20  

This statement is the output of the Oracle query 10th to 20th record, here is the reason to use RowNum RN, is to turn rownum into an instance, because rownum itself can only use <= comparison way, only to Narimi column, so you can do >= comparison.

In practical use, the most recent records are often requested, which requires sorting the records before taking RowNum <=

The General common

SELECT *  
FROM (SELECT a.*  
FROM torderdetail a  
ORDER BY order_date DESC)  
WHERE ROWNUM <= 10 

And there was a discussion in csdn about the 10 records that were taken, and some people gave such a statement

SELECT a.*  
FROM torderdetail a  
WHERE ROWNUM <= 10  
ORDER BY order_date DESC 

The reason for such a statement, mainly from the efficiency of the consideration, the preceding statement, is to perform a full table scan and then sort, then fetch 10 records, then a statement will not be full table scan, will only take out 10 records, it is obvious that the post-sentence efficiency will be much higher.

Why is it controversial, that is, in the order of execution of the dispute, is the first to perform a sort of 10 records, or take 10 records, and then sort? The results obtained from the two sequences are diametrically opposed, and the first 10 records of Oracle are sorted and then fetched by 10, that is, the last 10 are taken, and 10 are taken first, then the first 10 records are taken out. For this statement, it is generally considered that the order of execution takes 10 records to reorder.

So this statement should be an error. In fact, this is not the case, the execution order of this statement and order by field has a relationship, if you order by the field is PK, then the first sort, then take 10 (faster than the first statement faster), and the sort field is not PK, is the first fetch 10 reorder, at this time the result is not the same as the requirements, So the second way to do this is to make sure that the result is correct if the sort field is the primary key.

Row_number () over () This analysis function is provided starting from 9I, and the general purpose is almost the same as rownum.

The general Notation Row_number () over (order by order_date DESC) is generated in the same order as the RowNum statement, and the same is true for the rownum statement with order BY, so in this case the two usages are the same.

And for the group after the most recent 10 records, it is rownum can not be achieved, at this time only row_number could be achieved, row_number () over (the partition by Group field order by sort field) will be able to achieve the group after the number, For example, to take the last 10 order records for nearly one months each day.

SELECT *  
FROM (SELECT a.*,  
ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)  
rn  
FROM torderdetail a)  
WHERE rn <= 10  

RowNum's alternative usage, sometimes we encounter this demand, the output of all the days of the month, many people will worry, the database does not have such a table, how to output one months of all the days? It can be solved with rownum:

SELECT TRUNC (SYSDATE, ‘MM‘) + ROWNUM - 1  
FROM DUAL  
CONNECT BY ROWNUM <= TO_NUMBER 
(TO_CHAR (LAST_DAY (SYSDATE), ‘dd‘)) 

The above is an introduction to the top 10 records of Oracle queries.

In summary: Oracle queries 5th through 10th record paging query as:

SELECT * FROM (select A.*,rownum rn from (SELECT * from Scott.emp) a where rownum<=10) where rn>=5;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Query Top 10 records and paging query (5th to 10th record)