SQL techniques for efficient paging (using Oracle as an example)

The general paging method is like the following:The so-called "three-layer nesting" Statement),

 
 

  
  
SELECT *  
  
  
FROM   (SELECT a.*  
  
  
              ,rownum rn  
  
  
        FROM   (SELECT * FROM table_name) a  
  
  
        WHERE  rownum <= 40)  
  
  
WHERE  rn >= 21 
 
 

Performance problems may occur when a record with more than 1 million rows in a data table is displayed. The solution proposed by someone is to use the index. First, scan the index to find the rowid, and then use the rowid to find the required records. What if there is no index? Taking Oracle as an example, the following is a more general and efficient way of writing:

 
 

  
  
SELECT *  
  
  
FROM   <your table name> t  
  
  
WHERE  ROWID IN (SELECT rwd  
  
  
                 FROM   (SELECT t.rowid rwd  
  
  
                               ,rownum  n  
  
  
                         FROM   <your table name> t  
  
  
                         WHERE  rownum <= 1000000 + 10)  
  
  
                 WHERE  n >= 1000000) 
 
 

The optimization idea is to scan rowid only when jumping to the 1st million location, avoiding scanning all fields. When the number of fields is large, the speed will increase by 10 times. In this example, it takes less than 1 second to run on a common device. This method still has problems in the tens of millions or hundreds of millions of users, because the number of cycles has not been reduced. If a field such as rwd exists, the speed can be ensured through indexes, partitions, and other means. However, if we say it back, we will display hundreds of thousands of records on pages. Can we better solve the business objectives it wants to achieve?

This article is from the iData blog, please be sure to keep this source http://idata.blog.51cto.com/4581576/1121453