Efficient Paging for Oracle database queries

Linq provides Skip and Take APIs for paging. Because EntityFramework is used, it is better to use EFProfiler to view the generated SQL with curiosity. This mainly refers to the use of analysis functions such as row_numer () over (). You can find the starting point of the first row, and then retrieve 30 rows.

Linq provides Skip and Take APIs for paging. Because Entity Framework is used, you can use EFProfiler to view the generated SQL statements with curiosity. This mainly refers to the use of analysis functions such as row_numer () over (). You can find the starting point of the first row, and then retrieve 30 rows.

Linq provides Skip and Take APIs for paging. Because Entity Framework is used, you can use EFProfiler to view the generated SQL statements with curiosity. This mainly refers to the use of analysis functions such as row_numer () over (), where you can directly find the starting point of the 5,000th rows, and then retrieve 30 rows.

 

  
select * 
  
  from (select * 
  
          from (select t.*, 
  
                       row_number() OVER(ORDER BY null) AS "row_number" 
  
                  from yz_bingrenyz t) p 
  
         where p."row_number" > 5000 
  
        ) q 
  
 where rownum <= 30 
 

Comparative analysis:

It takes 1.3 s for the local machine to test the former, and 0.25 s for the latter. The difference can also be seen from the following execution plans.