Brief analysis of Oracle rownum __oracle

RowNum is a pseudo column that Oracle has provided from 8, numbering the results of SQL, always starting at 1, with the common use of paging output.

Like what:

      SELECT * from  table_temp t WHERE rownum <=-   The statement is the first 10 records of the query, similar to SQL Server top, but rownum queries that correspond to numbered intervals are more powerful

    SELECT * FROM (select A.*, rownum rn from Table_temp t) WHERE RN >= and RN <= 20--Queries 第10-20条 records.

Here the reason RowNum RN, is the rownum into an instance, because rownum itself can only use the <= comparison, only to Cheng Shi column, so you can do >= comparison

rownum The use of combined sorting:

       SELECT * FROM (select t.* to  table_temp T ORDER by  date DESC)  WHERE rownum <=-    Statement 1

      SELECT t.* from table_temp t WHERE rownum <= The By date DESC                    --Statement 2

The reason for such a statement, mainly from the efficiency of the consideration, statement 1, is to perform a full table scan before sorting, and then take 10 records, Statement 2 will not be full table scan, will only take out 10 records, it is obvious

The latter statement is much more efficient. However, the results of the two orders are diametrically opposed, the Oracle query before the first 10 records sorted and then fetch 10, that is, take the most recent 10, and first take 10, and then sorted, then take out the

The first 10 records. For statement 2, it is generally accepted that the order of execution is sorted by first taking 10 records. So statement 2 should be an error. But this is not the case, and the execution order of this statement is related to the field of the ordered by.

If you order by the field is PK, it is first sorted, and then take 10 (faster than the first statement), and the sorting field is not PK, is to take 10 before sorting, the result is not the same as the requirements, so the second

Be sure to make sure the results are correct when the sort field is the primary key.

row_number () over () This analytic function is provided from 9I, and the general use is similar to rownum.

The general wording row_number () over (the ORDER by order_date Desc) produces the same sequence as the rownum statement, and the same efficiency (for the ROWNUM statement that also has an order by), so in this

The two usages are the same in the case. And for the group to take the most recent 10 records, it is rownum can not be achieved, at this time only row_number could be implemented, Row_number () over (partition by the Group field order by the Sort field) can be implemented

After the group number, for example, to take nearly one months of the last 10 orders a day record.

SELECT * FROM (select t.*,  row_number () over (PARTITION to TRUNC (order_date) Order by Order_date DESC) RN from table _temp t)  WHERE RN <= 10

RowNum alternative use, sometimes we will 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 all the days of one months. It can be solved with rownum:

       SELECT TRUNC (sysdate, ' MM ') + ROWNUM-1 from  DUAL  

To sum up: Oracle query 5th to 10th record paging query is:

      SELECT * FROM (select T.*,rownum rn from (SELECT * scott.emp) t where rownum<=10) where rn>=5;