Dynamic Query Plan Caching via Statistics Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Dynamic query execution plans generated by Incremental Planning and Execution (IPE) are challenging to cache due to the reliance on intermediate relational results, which can lead to 'wrong answers' when underlying data changes, preventing effective reuse of cached plans for frequently processed queries.
Innovation Solution
A system and method for dynamic query plan caching that distinguishes between non-parameterized and parameterized queries, using feedback kind switching and alternative plan generation to control caching based on resource costs and feedback types, ensuring accurate and efficient execution by avoiding plans that rely on results feedback to prevent data inconsistencies.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If dynamic query execution plans are cached for reuse, then query processing efficiency is improved, but the risk of producing wrong answers increases when underlying data changes
Solution Approach 1:
The patent changes the parameter of feedback type from results feedback to statistics feedback. Statistics feedback uses aggregated statistical information rather than actual intermediate data values, allowing the cached plan to remain valid even when underlying data changes, thus maintaining both efficiency and reliability
Solution Approach 2:
The patent creates an alternative cached copy of the dynamic query execution plan that uses statistics feedback instead of results feedback. This copied plan can be safely reused without producing wrong answers, as it relies on statistical properties rather than specific data values that may change
2Productivity
If dynamic query execution plans are generated using results feedback from intermediate spool data, then plan optimization is improved, but the complexity of managing cache validity increases
Solution Approach 1:
The patent changes the feedback parameter from results-based to statistics-based. This simplifies cache management because statistical properties are more stable and less prone to invalidation than specific intermediate data values, reducing the complexity of tracking when cached plans become invalid
Solution Approach 2:
The patent extracts only the statistical feedback information from the intermediate spool data, leaving out the specific data values. This extraction creates a simplified representation that maintains optimization quality while reducing cache management complexity
Data Source
AI summary
A first query execution plan generated for a query on a second time the query was processed by a database is compared against a dynamically generated second query plan generated based on statistics only dynamic feedback for the second time the query is processed by the database. A determination is made on the second time as to whether to cache the first query execution plan, the second query execution plan, or no plan for third or more times the query is processed by the database. The query can be non-parameterized or parameterized.