Fine-Grained Database Cursor Invalidation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Legacy techniques for cursor invalidation in large databases often result in unnecessary invalidation of cursors, leading to performance degradation and costly recompilation, as they employ coarse-grained methods that invalidate all cursors dependent on altered data objects, even if only some are affected.
Innovation Solution
Implementing fine-grained cursor invalidation by annotating cursor metadata with extended attributes to selectively invalidate cursors based on specific rules, allowing for actions like validating, rolling invalidation, or metadata refresh, thereby minimizing unnecessary invalidation and optimizing performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If coarse-grained cursor invalidation is used, then all cursors dependent on altered data objects are invalidated, but this causes unnecessary revalidation of cursors that are not actually affected, leading to performance degradation and resource wastage
Solution Approach 1:
The patent segments the cursor invalidation process by introducing fine-grained dependencies that track specific data objects (tables, indexes, partitions) individually. Instead of invalidating all cursors broadly when any data object changes, the system divides the invalidation scope to only affect cursors with direct dependencies on the altered objects, thereby maintaining reliability while improving productivity.
Solution Approach 2:
The patent applies local quality by annotating cursor metadata with extended attributes that specify precise dependency information. This allows the system to differentiate between cursors that are truly affected by data object alterations and those that are not, enabling selective invalidation that maintains cursor validity where needed while avoiding unnecessary revalidation elsewhere.
2Device complexity
If coarse-grained cursor invalidation is used, then cursor dependencies are simplified, but this results in costly revalidation of multiple cursors including those not impacted by the alteration
Solution Approach 1:
The patent implements preliminary action by pre-computing and storing fine-grained dependency information in cursor metadata during cursor creation. This advance preparation allows the system to quickly identify and invalidate only the necessary cursors when data objects change, avoiding the time loss associated with broad revalidation while accepting the complexity of maintaining detailed dependency tracking.
3Productivity
If rolling invalidation technique is used, then recompile workload is spread over time, but valuable resources are consumed by recompiling cursors that may already be valid
Solution Approach 1:
The patent applies feedback by using the fine-grained dependency information to determine whether a cursor actually needs revalidation before initiating recompile operations. This feedback mechanism prevents the wasteful recompilation of cursors that remain valid despite data object alterations, thereby reducing energy loss while maintaining productive workload distribution through selective rolling invalidation.
Data Source
AI summary
A method commences upon receiving a database language statement pertaining to a database comprising database objects that are accessed by computing processes. The method analyzes the database language statement to form or identify a cursor object that has dependencies on one or more referenced data objects. The cursor object is associated with a set of cursor attributes that are stored in a cursor metadata data structure. By analyzing a subsequently-received database language statement, the method determines one or more operations to operate over at least one of the data objects. Rules are applied to the cursor attributes and/or to the operations and/or to objects referenced in the cursor so as to determine whether or not to invalidate the cursor object. The cursor may be considered at a later time for possible later invalidation or revalidation based on processing of later received database language statements and/or based on a later event.