Differences and linkages between Row_number and RowNum in Oracle (translation)

Http://www.tuicool.com/articles/bI3IBv

Attached problem: There is one SQL statement below:

Select  *from    (        Select  t.*, Row_number () over (frommytable t)WHERE RN between:  And:end     

The ORDER BY statement in SQL greatly reduces the processing speed, and if the order by is removed, the corresponding execution plan is greatly improved. If you switch to the following SQL:

SELECT  t.*, Row_number () over (byID) rnfrom    mytable tWHERE rownum between: and:end
             

Obviously, this SQL is wrong and cannot query the correct data information at all. Is there any other way to improve the query speed?
In view of the above problems, it is necessary to understand the difference between row_number and rownum, and how to use this information.

First look at how RowNum works, according to Oracle's official documentation:
If the rownum is larger than the comparison, this comparison will return false directly. For example, the following SQL statement will not return any data information:

SELECT  *from    employeesWHERE   1  

In the query, the first hit data is given a pseudo-column rownum of 1, then this condition is false. Second hit data because the first false will be the first data again, then the value is still assigned to 1, indicating that the condition is still false. All subsequent data will repeat this logic, and the last piece of data is not returned.

This is why the previous 2nd query should be converted to the following SQL statement:

Select  *from    (        Select  as RN        by paginator, id)WHERE rn between: and: End      

Next, you need to create some temporary data tables to see the execution performance of this SQL statement, we will create a temporary table, append the index, then populate the data, and finally analyze the query information for this SQL statement.

CREATETABLE MyTable (IDNumber (10)NotNULL, PaginatorNumber (10)NotNullValue VARCHAR2 (50))/AlterTABLE mytableadd constraint pk_mytable_id primary key (id)/create INDEX ix_mytable_paginator_id on mytable ( Paginator, id)/insertinto mytable (ID, Paginator, value) select level, level/10000 ,  ' Value ' | | levelfrom dualconnect by level <= 1000000/commit/ begin dbms_stats.gather_schema_stats (                

This SQL statement creates a table that contains 1 million data and creates a federated index.
Also, in this query, the Patinator field is unique in order to show the following phenomenon:
In a query, some data may appear multiple times in different paged queries, while some data may be based on not being queried
This is called paging chaos.

Then, we use Row_numer and rownum respectively to query, return 10 data information from 900001 to 900010.
Row_number ()

Select  *from    (        Select  t.*, Row_number () over (frommytable t)900010  

ID	Paginator	VALUE	RN
900001	90	Value 900001	900001
900002	90	Value 900002	900002
900003	90	Value 900003	900003
900004	90	Value 900004	900004
900005	90	Value 900005	900005
900006	90	Value 900006	900006
900007	90	Value 900007	900007
900008	90	Value 900008	900008
900009	90	Value 900009	900009
900010	90	Value 900010	900010
Rows fetched in 0.0005s (0.8594s)

SELECT STATEMENT  VIEW   WINDOW nosort stopkey   20090506_rownum. ix_mytable_paginator_id 

RowNum

Select  *from    (        Select  as RN        by paginator, id) t)900010   

ID	Paginator	VALUE	RN
900001	90	Value 900001	900001
900002	90	Value 900002	900002
900003	90	Value 900003	900003
900004	90	Value 900004	900004
900005	90	Value 900005	900005
900006	90	Value 900006	900006
900007	90	Value 900007	900007
900008	90	Value 900008	900008
900009	90	Value 900009	900009
900010	90	Value 900010	900010
Rows fetched in 0.0005s (0.7058)

SELECT STATEMENT  view   COUNT    view     20090506_rownum. ix_mytable_paginator_id   

As you can see from the above, the query using RowNum is slightly faster than the Row_number function.
Then looking at a row_number query, you can see that Oracle is smart enough to avoid sorting by using a federated index, and then you can quickly find the appropriate data information directly by using the Stopkey operation.
The rownum query also uses the index, but does not take advantage of the stopkey condition, just a simple count operation.
So, can you also let rownum use Stopkey? In the previous query, Oracle did not know that this RN is the alias of the inner query rownum, we can rewrite the query, use the rownum in the outer query, so that the stopkey condition can be exploited in the outer layer. This is the variant of our common Oracle3-layer pagination:

Select  *from    (        Select  as RN        by paginator, id) t)    

ID	Paginator	VALUE	RN
900001	90	Value 900001	900001
900002	90	Value 900002	900002
900003	90	Value 900003	900003
900004	90	Value 900004	900004
900005	90	Value 900005	900005
900006	90	Value 900006	900006
900007	90	Value 900007	900007
900008	90	Value 900008	900008
900009	90	Value 900009	900009
900010	90	Value 900010	900010
Rows fetched in 0.0005s (0.4714s)

SELECT STATEMENT  count stopkey  VIEW    count     20090506_rownum. ix_mytable_paginator_id   

In this query, Oracle leverages the Stopkey, which is only 471ms faster than the original.

If Row_number and rownum use the same execution plan, why rownum is significantly faster.
This is because Oracle's history is too long, and different times cause the same features to have different effects.

RowNum was introduced in Oracle6, published in 1988, at the time of what resources and conditions are not met, as a simple counter, is considered very simple and efficient.
With the development of the times, more needs are mentioned, at this time, an equivalent but more powerful function than RowNum is introduced, this is the Row_number function, which is introduced from the oracle9i. At this time, efficiency is no longer the only condition, so the realization of row_number is no longer the only indicator of efficiency.

Of course, if you have more requirements, such as grouping and so on, you need to use the Row_number function, but if you are simply a paged query, it is recommended to use rownum, which is why rownum is still so popular in the present era ( It is said that there is the offset paging operator in oracle12c, and the Row_number function is used internally, so that RowNum can retire.

The following is the original English: http://explainextended.com/2009/05/06/oracle-row_number-vs-rownum/

Differences and linkages between Row_number and RowNum in Oracle (translation)