Row_number () over order by and query paging Efficiency Analysis Using rownum

Actual test:

Database: 0.7 million data records

Query 10 million pages per page.
Row_number () elapsed time: 2.2 seconds
Rownum elapsed time: 1.3 seconds

Query 10 million pages per page.
Row_number () elapsed time: 4.5 seconds +
Rownum elapsed time: 3.8 seconds +

As you can see on the Internet, row_number () is the most efficient and has not been tested by big data.

We also hoped that the row_number () Paging code would be simpler and more efficient to optimize the current system! Disappointing!

Personal analysis: the new paging method (row_number () over (order by T. xxx DESC) Rn sorting method is not highly efficient than 3-layer nested sorting, because order by in over takes a long time (when the data volume reaches 100,000 levels ), in addition, rownum cannot be used in subqueries to reduce the data generated for the first time (because the rownum pseudo columns are arranged when data is retrieved ).

 

 

Row_number () over order by and query paging Efficiency Analysis Using rownum