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

The general paging method is like the following (the so-called three-layer Nesting Method), SELECT * FROM (SELECTa. * & nbs ..

The general paging method is like the following (the so-called three-layer Nesting Method), SELECT * FROM (SELECTa. * & nbs ..

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

When a record with 1 million rows in a data table is displayed, the U.S. server may have performance problems. 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:

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, let's talk about it again. In the US space, hundreds of thousands of records are displayed on pages. Can virtual hosts have a better solution to their business goals?

This article is from the "iData" blog. Please keep this source