Cursor paging for search indexes-Lindorm(Lindorm)-阿里云帮助中心

Batch data exports that use offset-based queries slow down as the result set grows, because each request must scan all preceding rows. Cursor paging avoids this by locating the next page directly from a saved cursor position, making it the efficient choice for iterating through large result sets from a search index.

Prerequisites

Before you begin, ensure that you have:

LindormTable version 2.8.2 or later. To check or upgrade your version, see Minor version update.

Constraints

The query must hit the search index. If you are unsure, use a HINT to explicitly specify the index. See HINT.
Cursor values are stateless. If data is inserted, updated, or deleted between two cursor-paged queries, the exported dataset may contain missing or duplicate rows.
Cursor paging is not supported for aggregate queries.
Use a LIMIT clause to set the number of rows per page. Omit OFFSET or set it to 0 — any other value skips rows on the current page.
Use an ORDER BY clause to sort results. All sort columns must be included in the search index. If no sort columns are specified, the search index applies its default sort order.

How cursor paging works

All cursor-paged queries use two special identifiers:

Identifier	Usage	Description
`_l_next_cursor_`	SELECT list	A virtual column that returns a cursor value (VARCHAR) for each row in the result.
`_l_current_cursor_`	WHERE clause	Passes the cursor position from the previous page to the next query.

The paging loop algorithm:

Run the first query with _l_next_cursor_ in the SELECT list and your filter conditions in the WHERE clause.
Inspect the _l_next_cursor_ value from the last row of the result:
- If _l_next_cursor_ is non-null, take that value and go to step 3.
- If _l_next_cursor_ is null, there are no more rows — the query is complete.
Add _l_current_cursor_ = '<cursor_value>' to the WHERE clause using AND, then run the query again.
Go to step 2.

cursor = null
do:
    result = query(filter_conditions, cursor, limit)
    process(result.rows)
    cursor = result.last_row._l_next_cursor_
while cursor != null

Page through a search index

Step 1: Set up test data

Create a table and search index, then insert test rows:

CREATE TABLE myTable (pk INT, c1 VARCHAR, c2 INT, PRIMARY KEY(pk));
CREATE INDEX idx USING SEARCH ON myTable (pk, c1, c2);

UPSERT INTO myTable (pk,c1,c2) VALUES (1,'a',1);
UPSERT INTO myTable (pk,c1,c2) VALUES (2,'b',2);
UPSERT INTO myTable (pk,c1,c2) VALUES (3,'c',3);
UPSERT INTO myTable (pk,c1,c2) VALUES (4,'d',4);
UPSERT INTO myTable (pk,c1,c2) VALUES (5,'e',5);
UPSERT INTO myTable (pk,c1,c2) VALUES (6,'f',6);
UPSERT INTO myTable (pk,c1,c2) VALUES (7,'g',7);
UPSERT INTO myTable (pk,c1,c2) VALUES (8,'h',8);
UPSERT INTO myTable (pk,c1,c2) VALUES (9,'i',9);

Note

After inserting data, wait about 15 seconds for the data to sync to the search index before running queries.

Step 2: Run the first query

Add _l_next_cursor_ to the SELECT list. The LIMIT clause sets how many rows are returned per page.

SELECT c1, c2, _l_next_cursor_ FROM myTable WHERE c2<=7 LIMIT 3;

This query matches 7 rows and returns the first 3:

+----+----+--------------------------+
| c1 | c2 | _l_next_cursor_          |
+----+----+--------------------------+
| e  | 5  | AVd6UXlPVFE1TmpjeU9UbGQ= |
| b  | 2  | AVd6UXlPVFE1TmpjeU9UbGQ= |
| g  | 7  | AVd6UXlPVFE1TmpjeU9UbGQ= |
+----+----+--------------------------+

Take the _l_next_cursor_ value from the last row — AVd6UXlPVFE1TmpjeU9UbGQ= — and use it in the next query.

Step 3: Get the next page

Pass the cursor value from the last row as _l_current_cursor_ in the WHERE clause, combined with your original filter conditions using AND:

SELECT c1, c2, _l_next_cursor_ FROM myTable
WHERE _l_current_cursor_ = 'AVd6UXlPVFE1TmpjeU9UbGQ='  -- cursor from previous page
  AND c2<=7
LIMIT 3;

+----+----+------------------------------+
| c1 | c2 | _l_next_cursor_              |
+----+----+------------------------------+
| d  | 4  | AVd6RXlPRGcwT1RBeE9Ea3lYUT09 |
| a  | 1  | AVd6RXlPRGcwT1RBeE9Ea3lYUT09 |
| c  | 3  | AVd6RXlPRGcwT1RBeE9Ea3lYUT09 |
+----+----+------------------------------+

Note

The _l_current_cursor_ condition only passes the cursor position and does not affect other filter conditions. Always combine it with other conditions using AND.

Step 4: Continue until done

Repeat step 3, always taking _l_next_cursor_ from the last row of the current result. When _l_next_cursor_ returns null, the query is complete:

+----+----+-----------------+
| c1 | c2 | _l_next_cursor_ |
+----+----+-----------------+
| f  | 6  | null            |
+----+----+-----------------+

Use the implicit projection method

To avoid adding _l_next_cursor_ to every SELECT list, use the _l_allow_cursor_ HINT:

SELECT /*+ _l_allow_cursor_ */ c1, c2 FROM myTable WHERE c2<=7 LIMIT 3;

This is equivalent to explicitly including _l_next_cursor_ in the SELECT list. The returned cursor values and all subsequent paging operations work the same way.

To get the next page with the implicit method:

SELECT /*+ _l_allow_cursor_ */ c1, c2 FROM myTable
WHERE _l_current_cursor_ = 'AVd6UXlPVFE1TmpjeU9UbGQ='
  AND c2<=7
LIMIT 3;

For more information about HINT syntax, see HINT.