Database Paging Index Table for Stable Query Offset
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems using the OFFSET and FETCH clauses for query paging require multiple query executions and rely on client-side state management, which can lead to instability and inefficiency, especially when data changes or transaction isolation is not guaranteed.
Innovation Solution
Generating an index table with sequential integers and corresponding item identifiers, and a page index table that determines the item identifiers for the first and last items on each page, allowing for efficient retrieval of data pages without the need for sorting the primary table or examining unnecessary rows.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If OFFSET and FETCH clauses are used for query paging, then the number of rows sent to client application is limited, but multiple query executions are required and client application must track state
Solution Approach 1:
The patent pre-calculates and stores offset values in a separate table during an initial process. This preliminary action allows the actual query execution to simply retrieve pre-computed offset values rather than calculating them dynamically, reducing query execution complexity while maintaining the ability to limit rows sent to the client.
Solution Approach 2:
The patent introduces a separate table that stores offset values as an intermediary structure. This intermediary table acts as a mediator between the query paging requirement and the actual data retrieval, allowing the system to track state and manage offsets without requiring complex client-side state management or multiple query executions.
2Ease of operation
If OFFSET and FETCH clauses are used for query paging, then pagination is implemented, but query stability is compromised when data changes or transaction isolation is not guaranteed
Solution Approach 1:
The patent performs the offset calculation and storage in a preliminary action during an initial process execution. By pre-computing and storing these offsets in a separate table, the system establishes a stable reference framework that remains consistent even when underlying data changes, thereby improving query result stability while maintaining pagination functionality.
Solution Approach 2:
The patent creates a separate table that copies and stores offset values independently from the main data table. This copying approach decouples the pagination mechanism from the underlying data, ensuring that query stability is maintained even when the original data changes or transaction isolation is not guaranteed, as the offset table provides a stable reference framework.
3Reliability
If ORDER BY clause with unique column is used, then stable results are achieved, but sorting the primary table is required which increases complexity
Solution Approach 1:
The patent introduces a separate table that stores offset values as an intermediary structure. This intermediary table provides a stable reference framework for pagination without requiring the primary table to be sorted. The offset table acts as a mediator that enables stable results by maintaining consistent offset values independent of the primary table's sort order or data changes.
Solution Approach 2:
The patent segments the pagination functionality into a separate table that stores offset values independently from the main data table. This segmentation allows the system to achieve result stability through the offset table rather than requiring the primary table to be sorted, reducing the complexity of table sorting while maintaining reliable pagination.
Data Source
AI summary
Paging of data includes querying a table of data to determine an item identifier for each item in the table. An index table including an index identifier column of sequential integers and a corresponding item identifier column for each item in the table is generated and stored in a memory. A page index table including the item identifier that corresponds to a first index identifier and each item identifier that corresponds to one plus a pagesize multiple of the index identifier is generated and stored in a memory. A desired page of data is retrieved using the determined item identifier corresponding to the first item on the desired page and either the determined item identifier corresponding to the first item on a next page after the desired page or a determined item identifier corresponding to the last item on the desired page.