Dynamic Join Index Caching for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query optimization techniques in relational database management systems do not fully address the issue of redundant computations in shared query expressions, leading to suboptimal performance when similar queries are executed multiple times.
Innovation Solution
Implementing a Dynamic Join Index (DJI) caching mechanism within the Query Expression Repository (QER) to cache results of frequently used query expressions, which are then reused to reduce redundant computations and improve query performance by rewriting subsequent queries to utilize cached results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If query expression results are cached in a standard QER, then query performance improves through reuse, but storage space is wasted on caching results of infrequently used queries
Solution Approach 1:
The patent implements a dynamic join index that automatically adapts its caching behavior based on query frequency and resource availability. The system monitors query execution patterns and dynamically adjusts which query expressions are cached and for how long, transitioning from static caching to adaptive caching that responds to changing workload characteristics.
Solution Approach 2:
The system changes the state of cached query results from permanent to temporary, introducing time-to-live parameters and frequency-based expiration. Query results are cached with dynamic validity periods that adjust based on usage patterns, allowing the system to optimize between storage utilization and query performance by expiring old or rarely accessed results.
2Loss of time
If all query expression results are cached, then redundant computations are eliminated, but memory overhead increases significantly
Solution Approach 1:
Instead of caching all query expression results, the system selectively caches only those results that meet specific criteria such as high frequency of reuse, large computational cost, and small result size. This partial caching approach focuses resources on the most beneficial cases rather than attempting to cache everything.
Solution Approach 2:
The patent applies different caching strategies to different query expressions based on their individual characteristics. High-frequency, expensive-to-compute queries receive aggressive caching, while low-frequency or simple queries use minimal or no caching, creating a differentiated caching policy that optimizes overall system performance.
3Productivity
If query results are cached indefinitely, then query performance improves through maximum reuse, but the system cannot adapt to data changes
Solution Approach 1:
The system implements periodic validation and expiration of cached query results based on underlying data change detection. Query results are cached for fixed periods and automatically refreshed or invalidated when the underlying data changes, creating a rhythmic pattern of caching and validation that balances performance and freshness.
Solution Approach 2:
The patent incorporates feedback mechanisms that monitor data changes and automatically adjust caching behavior. When data modification patterns are detected, the system receives feedback to invalidate or refresh relevant cached results, ensuring that cached data remains consistent with the current state of the database.
Data Source
AI summary
An apparatus, method and computer program product for query optimization in a Relational Database Management System (RDBMS), wherein an optimizer accesses a query expression repository (QER) storing planning and execution information for QEs from previous queries, wherein the QEs comprise table relations, intermediate results and/or final results of operations in the previous queries. Additionally, dynamic join indexes representing QE results are created for high-value QEs selected from the QER and maintained within a DJI repository. During query plan creation for a current or subsequent query, the optimizer searches the QER and DJI repository for DJIs created for high-value QEs corresponding to QEs contained in the current or subsequent query. DJIs corresponding to the matching QEs are used in the query planning phase to rewrite the current or subsequent user query so that stored QE results are used to answer QEs contained in the current or subsequent query.