Database Object Validation for Query Plan Reuse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in determining the reusability of query plans due to changes in metadata, leading to potential performance regression, as existing methods may incorrectly invalidate query plans even when the logical structure remains unchanged.
Innovation Solution
An improved object validation method that assesses the reusability of query plans by comparing the logical structures of objects involved in the query with their captured versions, stored in object definition and mapping tables, to determine if the query plans can be safely reused despite changes in object identifiers and versions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If query plans are reused to improve performance, then query execution speed is improved, but query plan stability deteriorates when object metadata changes
Solution Approach 1:
The patent changes the validation parameter from metadata-based (OID, VID) to logical structure-based comparison. By comparing the actual logical structure of objects rather than their identifiers, the system can detect when objects are semantically equivalent despite metadata changes, enabling safe query plan reuse while maintaining stability.
Solution Approach 2:
The patent replaces the mechanical metadata comparison system with a logical structure interpretation system. Instead of directly comparing object identifiers and versions, the system translates objects into their logical structures and compares those representations, providing a more robust validation mechanism that ignores superficial metadata changes.
2Reliability
If query plans are invalidated when object metadata changes, then query plan stability is maintained, but productivity decreases due to unnecessary plan regeneration
Solution Approach 1:
The patent changes the validation criterion from strict metadata equality to logical structure equivalence. This allows query plans to be reused when objects have the same logical structure even if their metadata (OID, VID) has changed, preventing unnecessary plan regencation and improving productivity while maintaining stability.
Solution Approach 2:
The patent performs preliminary validation of object logical structures before allowing query plan reuse. By pre-computing and storing the logical structure representations of objects in the query plan cache, the system can quickly validate whether a captured plan is still valid without regenerating it, thus improving efficiency.
3Device complexity
If metadata-based validation is used to determine query plan reusability, then implementation simplicity is maintained, but measurement precision of object structural changes deteriorates
Solution Approach 1:
The patent substitutes the simple metadata comparison mechanism with a logical structure interpretation mechanism. By translating objects into their logical structures and comparing those, the system achieves precise detection of actual structural changes while maintaining reasonable implementation complexity through systematic translation and comparison procedures.
Solution Approach 2:
The patent introduces logical structure representation as an intermediary between objects and validation decisions. Instead of directly comparing metadata or object identities, the system compares the logical structure intermediaries that represent objects' actual structure, providing precise change detection while keeping the validation process manageable.
Data Source
AI summary
A computer implemented method can receive a request for a fallback query execution plan of a query in a database system. The query involves an object of the database system. The object is associated with a captured object. The method can evaluate whether the object and the captured object have the same logical structure. Responsive to an indication that the object and the captured object have the same logical structure, the method can reuse the fallback query execution plan to execute the query. The evaluating includes identifying a logical structure of the captured object stored in an object definition table, obtaining a current logical structure of the object, and comparing the logical structure of the captured object with the current logical structure of the object.