Asynchronous Index Query Consistency via Flashback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Asynchronously maintained indexes in database systems often lead to inconsistencies between the index and its base table, resulting in queries not reflecting the current state of the base table, necessitating improved techniques for handling queries against such indexes.
Innovation Solution
The solution involves computing queries to be consistent with a previous logical point in time when both the index and its base table were synchronized, using a flashback mechanism to rewrite queries and store synchronization points, such as System Change Numbers, to ensure consistency, and updating the index periodically or at commit transactions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the index is maintained asynchronously to improve update performance, then the update time is reduced, but the consistency between the index and base table deteriorates
Solution Approach 1:
The patent applies preliminary action by capturing the state of the base table at a specific synchronization point before asynchronous updates occur. The query optimizer stores this snapshot state and uses it when executing queries, ensuring that queries reflect the consistent state at the time of capture rather than potentially inconsistent current states.
Solution Approach 2:
The patent introduces a synchronization point mechanism as an intermediary between the base table and the index. This synchronization point acts as a mediator that tracks when consistency was established, allowing queries to access a consistent snapshot state without requiring continuous synchronization during updates.
2Reliability
If the index is maintained synchronously to ensure consistency, then the consistency between index and base table is improved, but the update time increases significantly
Solution Approach 1:
The system performs the consistent state capture in advance at synchronization points rather than during every update operation. This allows the heavy lifting of maintaining consistency to be done periodically at designated moments, reducing the impact on individual update operations.
Solution Approach 2:
Instead of continuous synchronous maintenance, the patent implements periodic synchronization where the index is updated at specific intervals or transaction commit points. This periodic approach maintains consistency at critical moments while allowing faster updates between synchronization points.
3Loss of time
If queries are executed against the current state of the index, then query response time is improved, but the results may not reflect the current state of the base table
Solution Approach 1:
The query optimizer performs preliminary action by determining the appropriate synchronization point before executing the query. It then retrieves the pre-captured snapshot state at that synchronization point, ensuring that the query results reflect the consistent state at the time of capture rather than potentially inaccurate current state.
Solution Approach 2:
The patent uses copying by creating a snapshot copy of the base table state at the synchronization point. Queries then operate on this copied snapshot state, which preserves the consistent state at the time of capture while allowing fast query execution without accessing the potentially inconsistent current state.
Data Source
AI summary
A method and apparatus for computing queries in a way that is consistent with an index and the underlying base table is provided. A query is computed such that it is consistent with the state of an index and base table as of the last point at which the index and table were synchronized. For example, if an index is maintained asynchronously then it is not necessarily consistent with its base table when a query is to be run against the table and the index. Therefore, the query is computed such that it will be consistent with the last point at which the index and the table were synchronized. In one embodiment, a System Change Number (SCN) of the last successful synchronization operation is used to perform a flashback query on the table.