If the conventional pagination cannot meet your business requirements, you can implement cursor pagination in queries. This method records the page cursor to efficiently query desired data from large datasets and minimize the performance overhead incurred by extensive scanning operations. This topic describes how to use the cursor pagination feature and relevant precautions.
Prerequisites
The LindormTable version is 2.7.8 or later. You can view the LindormTable version and update the minor version to 2.7.8 or later in the console.
Precautions
Cursor pagination supports only simple queries and cannot handle queries that contain aggregate functions such as SUM and COUNT,
ORDER BYclauses, orGROUP BYclauses.Cursor pagination must be used in combination with the
OFFSETandLIMITclauses.
Overview
Conventional pagination uses the OFFSET and LIMIT clauses to filter data in memory. When you handle large amounts of data, deep pagination is inefficient because a large amount of data is scanned and skipped.
Lindorm provides the cursor pagination feature. This feature allows you to use the OFFSET and LIMIT clauses in query statements to implement pagination logic. When you use cursor pagination in queries, the system locates the data position based on cursors. This prevents extensive data scanning and skipping operations and significantly boosts query efficiency when you need to handle large amounts of data.
Use cursor pagination in queries
Explicit projection (recommended)
Use the _l_next_cursor_ hint in the query statement to return the cursor of the next page. Sample code:
SELECT <column_identifier1>, <column_identifier2>, _l_next_cursor_ FROM <table_identifier> LIMIT 0, 5;If you use cursor pagination in a query, the _l_next_cursor_ column is returned and contains a VARCHAR value that indicates the cursor of the next page.
To query data in the next page, you can use the _l_current_cursor_ hint in the WHERE clause. In the _l_current_cursor_ hint, you need to use the AND logic to specify the cursor value and the original filter condition, such as c1=1. Sample code:
SELECT <column_identifier 1>, <column_identifier 2>, _l_next_cursor_ FROM <table_identifier> WHERE _l_current_cursor_ = '<Cursor value>' AND <Filter condition> 1 LIMIT 5, 5;Implicit projection
If you cannot determine the column to be queried, we recommend that you use implicit projection. For example, you need to query a dynamic column.
If the list of projected columns is specified by an asterisk (*), the system returns only the predefined columns in the table. The cursor column is not included in the result. To return the cursor of the next page, add the /*+ l_allow_cursor */ hint after the SELECT keyword. Sample code:
SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> LIMIT 0, 5;If you use cursor pagination in a query, the _l_next_cursor_ column is returned and contains a VARCHAR value that indicates the cursor of the next page. To query data on the next page, you can use the _l_current_cursor_ hint in the WHERE clause. In the _l_current_cursor_ hint, you need to use the AND logic to specify the cursor value and the original filter condition, such as c1=1. Sample code:
SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> WHERE _l_current_cursor_ = '<Cursor value>' AND <Original filter condition> LIMIT 5,5;Different cursor values are returned in the _l_next_cursor_ column. You must use the last cursor value as the cursor of the next page.
Examples
Execute the following statement to create a sample table named tb_cursor_test:
-- Create a sample table.
CREATE TABLE tb_cursor_test(c1 INT , c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1));
-- Insert data into the table.
UPSERT INTO tb_cursor_test(c1, c3) VALUES (1, 'c3_1'),(2, 'c3_2'),(3, 'c3_3'),(4, 'c3_4'),(5, 'c3_5');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (6, 'c3_6'),(7, 'c3_7'),(8, 'c3_8'),(9, 'c3_9'),(10, 'c3_10');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (11, 'c3_11'),(12, 'c3_12'),(13, 'c3_13'),(14, 'c3_14'),(15, 'c3_15');Use cursor pagination to query data on the first page.
ImportantWhen you use cursor pagination to query data on the first page, you must use the _l_next_cursor_ hint in the query statement.
SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test LIMIT 0, 5;Sample output:
+----+------+------+------------------------------+ | c1 | c2 | c3 | _l_next_cursor_ | +----+------+------+------------------------------+ | 1 | null | c3_1 | AAAABIAAAAYFAAAABk5PUk1BTA== | | 2 | null | c3_2 | AAAABIAAAAYFAAAABk5PUk1BTA== | | 3 | null | c3_3 | AAAABIAAAAYFAAAABk5PUk1BTA== | | 4 | null | c3_4 | AAAABIAAAAYFAAAABk5PUk1BTA== | | 5 | null | c3_5 | AAAABIAAAAYFAAAABk5PUk1BTA== | +----+------+------+------------------------------+In the query result,
AAAABIAAAAYFAAAABk5PUk1BTA==in the_l_next_cursor_column indicates the cursor of the next page.To query data on the second page, specify the cursor value in the WHERE clause. The cursor value is returned in the query result of the first page. Sample code:
SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test WHERE _l_current_cursor_ = 'AAAABIAAAAYFAAAABk5PUk1BTA==' LIMIT 5, 5;Sample output:
+----+------+-------+------------------------------+ | c1 | c2 | c3 | _l_next_cursor_ | +----+------+-------+------------------------------+ | 6 | null | c3_6 | AAAABIAAAAsKAAAABk5PUk1BTA== | | 7 | null | c3_7 | AAAABIAAAAsKAAAABk5PUk1BTA== | | 8 | null | c3_8 | AAAABIAAAAsKAAAABk5PUk1BTA== | | 9 | null | c3_9 | AAAABIAAAAsKAAAABk5PUk1BTA== | | 10 | null | c3_10 | AAAABIAAAAsKAAAABk5PUk1BTA== | +----+------+-------+------------------------------+Query data on the third page. Sample code:
SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test WHERE _l_current_cursor_ = 'AAAABIAAAAsKAAAABk5PUk1BTA==' LIMIT 10, 5;Sample output:
+----+------+-------+-----------------+ | c1 | c2 | c3 | _l_next_cursor_ | +----+------+-------+-----------------+ | 11 | null | c3_11 | null | | 12 | null | c3_12 | null | | 13 | null | c3_13 | null | | 14 | null | c3_14 | null | | 15 | null | c3_15 | null | +----+------+-------+-----------------+No cursor value is returned in the result because the third page is the last page.