Query Plan Fragment Caching for Faster Plan Adaptation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Determining query plans in database systems is resource-intensive and inefficient when modifications are needed, as caching only the selected plan leads to repeated calculations and wasted work on unselected plans.
Innovation Solution
Cache query plan fragments of unselected plans to facilitate rapid modification and reuse, allowing the database system to automatically diagnose and remediate problematic plans by replacing fragments with cached alternatives based on performance metrics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If only the selected query plan is cached, then memory usage is reduced, but significant work is repeated when modifying the plan
Solution Approach 1:
The patent divides the query plan into fragments that can be independently cached and reused. Instead of caching the entire selected plan or discarding unselected plans, the system segments the evaluation work into reusable fragments, allowing selective reuse of useful portions while avoiding redundant calculations.
Solution Approach 2:
The patent performs preliminary evaluation of multiple query plans and caches their fragments before the final plan is selected. This preliminary action allows the system to reuse evaluation results when modifying plans, avoiding the need to re-evaluate unselected plans from scratch.
2Reliability
If multiple query plans are evaluated thoroughly, then plan quality is improved, but processor and memory resources are consumed excessively
Solution Approach 1:
The patent applies local quality by evaluating and caching only the necessary fragments of query plans rather than completely evaluating all possible plans. This allows the system to maintain high plan quality for critical portions while reducing resource consumption for less important evaluation work.
Solution Approach 2:
The patent creates copies of query plan fragments from unselected plans and stores them in a cache. These copies can be reused when modifying the selected plan, eliminating the need to re-evaluate and resource-intensive complete plan determinations.
3Adaptability or versatility
If query plan modifications are made frequently, then adaptability is improved, but repeated determination process increases overhead
Solution Approach 1:
The patent implements a dynamic caching mechanism that adapts to plan modifications. When a plan is modified, the system dynamically determines which fragments to reuse from the cache and which need to be re-evaluated, optimizing the balance between adaptability and overhead.
Solution Approach 2:
The patent recovers useful work by caching query plan fragments from unselected plans and reusing them when modifications are needed. Instead of discarding the evaluation work on unselected plans, the system recovers and reuses the useful fragments, reducing the overhead of repeated determinations.
Data Source
AI summary
Techniques are disclosed relating to determining query plans for execution by database systems. In various embodiments, a query optimizer determines a first query plan to implement a query requesting data from a database system. The determining includes selecting one of a plurality of query plans evaluated based on a cost analysis and caching plan fragments of the unselected query plans. The database system can then determine a second query plan for the query by replacing a plan fragment in the first query plan with one of the cached plan fragments of the unselected query plans.