Considerations for offset predicate when using Seek method for paging

Seek method pagination is a popular paging concept recently. Its core idea is: no longer rely on index as the offset, but use conditional expressions as the basis for paging. The specific principle I am here no longer nonsense, interested friends can go to search.

As the "conditional expression" becomes the basis for paging, how to prepare the paging conditional expression (seek predicate) becomes the key.

First of all, the prerequisite for using seek method pagination is that it must be an ordered result set. Corresponding to SQL script, an order by clause is required.

Here are some things to pay attention to when organizing seek predicate:

1. Order by's column (or more columns) must have a column that contains the unique value of the entire table (that is, a column like a primary key, and the value in the column must be not null and unique). The primary key column fits this feature.

2. The columns that appear in order by must also appear in the where clause, and the order must not be wrong. For example, order by An asc, B asc, C asc, then where. And (A, B, C) > (x, y, z). Other predicate that has nothing to do with seek predicate can appear in the where, but make sure that the column that appears in the order by appears in the where clause

3. The columns of order by must be in the same direction, not order by An asc, B desc, C asc.

4. (a, B, C,.) > (x, y, z,.) Must correspond to the direction in the order by Rules:

Page turning direction

Order by direction

Seek predicate operator in where

next page

ASC

>

DESC

If your result set order is ASC, then if you want to turn to the next page, your seek predicate should use > as the logical operator; if you want to turn to the previous page, the operator of seek predicate should be or ('Yan',' Male', 1200) order by A, B, Climit 30

Executing the above SQL results in 30 records (if any) on the next page, and then repeats the operation that just collected the condition values, for example, the last record of the 30 records on the second screen. Reassemble the new x, y, z values into the SQL of the third screen:

Select... from... where... And (A, B, C) > ('Ye',' Male', 335) order by A, B, Climit 30

Executing the above SQL results in 30 records (if any) on the next page. Again and again until all the data has been loaded.

(a, B, C) > ('Ye',' Male',)

This thing is seek predicate, which can be understood as condition offset. The traditional paging method uses index offset. In traditional paging, you need to know how many records there are and how many records are displayed on each screen (batch size), then query the entire result set (in DBMS's cache), and then use index offset offset skim offset rows to read batch records. The process of complete set query and skim is fatal. The later you turn the page, for example, after 1000 pages, the slower the skim will be, the greater the memory overhead, and the heavier the database burden.

But seek method paging is different, the database does not need to query all the data that meets the criteria, he only needs to locate the first record that meets the condition according to your condition, and then read the batch size record back to complete the reading of that page.

At present, many databases support this (A, B, C) op (x, y, z) writing method of seek predicate, but JPA does not. However, according to the mathematical knowledge we have learned, we can change the inequality into an equivalent formula.

(a, B) > (x, y) = A > x OR (A > x AND B > y)

(a, B, C) > (x, y, z) = A > x OR (An OR x AND (B > y AND C > z)

And so on, you can write a recursive automatic decomposition assembly (A, B, C,.) > (x, y, z,.)

Tips:

If a null value is encountered for the column value of order by, then A > null OR (A=null AND B > y). This writing is supported in postgresql v9.4. If your database does not support it, you can write > null as An is not null OR (An is null AND B > y)