Implementation of Oracle paging sorting and paging data duplication problem

For relational databases, it is common practice to write directly to the SQL pull data in the list. But this poses a problem: when the amount of data is large to a certain extent, the system memory will consume light sooner or later. In addition, network transmission is also a problem. If there are 10 million data, the user wants to see the last one, even if there is enough memory to transmit so much data on the network for two hours, I am afraid few users have such patience. Paging is therefore necessary.

Now online forums, blogs and so on, basically will have paging function, some are SQL paging, some may be nosql in other ways to page, there are very mature things. Based on my own experience, this paper takes Oracle as an example to talk about simple SQL paging and sorting problems, and some help to the people who are just ready to do the paging with SQL, so Daniel won't have to look at it.

Suppose there is a TAB001 table in the Oralce database, the primary key is ID, there are 10 million records, and the index is all there. We have a requirement that a record of the specified conditions be listed on the interface, with the original SQL as follows:

Select Id,name,atype,createdate,creator,astatus from TAB001 where atype= ' Some_type '

If you want to sort, for example, to sort by creator, we'll add a sentence after SQL:order by Createor desc

Now, we find this SQL down there are 5 million records, obviously, if not paging, the system will easily be tilted off. So we're going to split pages.

Before paging, we may have to put a few buttons and status display on the interface: previous page, next page, first page, last page, x bar per page, Total m page, current nth page, skip to page N, etc. Obviously, we are paging through the following steps:

1. Calculate the total number of records;
2. The total number of pages is calculated based on the total record count and the number of records per page;
3. Calculates the start and end record numbers based on the page number currently being displayed;
4. Raw content page SQL, executed, returns the data on this page, and displays it.

First, calculate the total number of records. This simple, nested a SELECT count (*) on the line:

Select COUNT (*) from (select Id,name,atype,createdate,creator,astatus fromTAB001 whereAtype= ' Some_type ') xx

Then, the total number of pages =ceil (Total records/records per page), less than one page is also treated as one page.

Then, assuming that this is page N, the start and end record number for this page is:

Start record number =n* number of records per page

End record number =min ((n+1) * Number of records per page-1, Total records)

Finally, the raw component page SQL. Because paging requires a record number, you first nest a subquery to generate RowNum:

Select RowNum as Recordno from ( SelectId,name,atype,createdate,creator,astatus fromTAB001 whereAtype= ' Some_type ') xx

In this way, we have a record number, you can filter the record number, only select the record number after the beginning of this page, the end of the record number of records:

Select xxx.* from (select RowNum as Recordno from ( SelectId,name,atype,createdate,creator,astatus fromTAB001 whereAtype= ' Some_type ') xx) xxxwhere Recordno >=: Start record number and Recordno <=: End record number

At this point, it seems that the paging SQL has been completed, on the surface of the SQL is quite correct, it seems to run no problem. But after our practice, this SQL is not safe, in some cases it will go wrong, because it is not sorted. In the case of paging, the data for the first and second pages is from two relatively independent SQL, and if there is no sort, the results returned by SQL first and second executions are inconsistent.

What do you mean by inconsistency? Assuming there is a SQL that is not sorted, we execute SQL two times:

• 1, 2, 3, 4, 5 total 5 records are returned after the first execution
• The second execution will return 1, 2, 3, 4, 5 total 5 Records

Most of the time, the order of the two returned results is exactly the same. Unfortunately, perhaps the database has a problem, perhaps someone changed the data, anyway, sometimes it will be different, such as the second implementation of the 2nd and 4th reversed, the return is 1, 4, 3, 2, 5 a total of 5 records, as follows:

• First time: 1, 2, 3, 4, 5
• Second time: 1, 4, 3, 2, 5

Let's say we paged this SQL, 3 records per page, a total of two pages, and normally the result is this:

• When you pull the first page, execute the first SQL, sort by 1, 2, 3, 4, 5, and return 1, 2, 33 records
• Pull the second page, execute the first SQL, sorted by 1, 2, 3, 4, 5, return 4, 52 records

But if there is a sort of confusion, the result is this:

• When you pull the first page, execute the first SQL, sort by 1, 2, 3, 4, 5, and return 1, 2, 33 records
• Pull second page, execute second SQL, sort by 1, 4, 3, 2, 5, return 2, 52 records

As a result we will find that the paging result is not normal, 2 this record appears two times, 4 disappears. Normally, we don't notice data loss, but we notice that the paging data is duplicated.

What do we do? Let's add a sort, sort clauses to the innermost SQL so that the results of pagination are sorted. For example, an ORDER BY clause sorted by name, category, or author:

Select xxx.* from (select RowNum as Recordno from ( SelectId,name,atype,createdate,creator,astatus fromTAB001 whereAtype= ' Some_type ' ORDER by Name,atype,creator) xx xxxwhere reco Rdno >=: Start record number and Recordno <=: End record number

Does that make it possible? The answer is no, because the values of these fields are not unique. Consider an extreme situation where the names, categories, and authors of the 5 million records in this table are exactly the same. The result is still unordered.

Finally, the solution to this problem is to be sure to sort by the ID primary key. Regardless of the number of order by fields, the last side must be preceded by an ID primary key:

Select xxx.* from (select RowNum as Recordno from ( SelectId,name,atype,createdate,creator,astatus fromTAB001 whereAtype= ' Some_type ' ORDER by Name,atype,creator, ID) xx) Xxxwhere Recordno >=: Start record number and Recordno <=: End record number

Because the primary key ID is unique, sorting by ID ensures that each execution of paged SQL is consistent in the same order as long as the ID is the same.

Implementation of Oracle paging sorting and paging data duplication problem