Caching Query Plans Using Timestamp Validity Periods
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Caching query execution plans that contain references to CURRENT_TIMESTAMP or TEMPORAL_TIMESTAMP is challenging due to their temporal nature, making cached plans useful only at exact moments, which is unrealistic for CURRENT_TIMESTAMP and unlikely for TEMPORAL_TIMESTAMP, leading to inefficient query processing.
Innovation Solution
The system determines the validity period of a query plan by using temporarily replaced timestamp values for CURRENT_TIMESTAMP and/or TEMPORAL_TIMESTAMP, allowing for partition elimination and join index selection, and caches the plan if the resulting partition elimination and join index selection are stable over time, enabling reuse of the plan within a valid time period.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a query execution plan is cached for a query containing CURRENT_TIMESTAMP or TEMPORAL_TIMESTAMP, then query processing efficiency is improved through plan reuse, but the plan becomes invalid immediately or very quickly since timestamps change continuously
Solution Approach 1:
The system dynamically determines the validity period of cached query plans by evaluating timestamp expressions and calculating time ranges during which the plan remains optimal. Instead of assuming immediate invalidity, the system adapts the cache duration based on the specific timestamp conditions in the query, allowing plans to be reused within their valid time windows while automatically invalidating them when timestamps change beyond the valid period.
2Device complexity
If the system determines plan validity by checking exact timestamp matches, then plan caching is simple to implement, but plan reuse becomes unrealistic since exact timestamp matches rarely occur
Solution Approach 1:
The system transforms the caching approach by changing from exact timestamp parameter matching to range-based parameter matching. Instead of requiring queries to match the exact timestamp when the plan was created, the system calculates a validity time range for each cached plan and allows reuse as long as the current timestamp falls within that range. This parameter transformation enables practical plan reuse while maintaining reasonable caching complexity.
3Loss of time
If the system caches plans without determining validity periods, then caching operation is fast and simple, but incorrect plans may be reused leading to suboptimal query execution
Solution Approach 1:
The system performs preliminary validity determination when caching the query plan, calculating the time range during which the plan remains optimal before storing it in the cache. This preliminary action includes evaluating timestamp expressions, determining partition eligibility, and establishing the validity period. When a query arrives, the system only needs to check if the current timestamp falls within the pre-calculated validity range, making the validation process efficient while ensuring plan correctness.
Data Source
AI summary
A computer system programmed as a database receives a query Q1. Q1 includes a predicate. The predicate includes a reference to a current timestamp of time TT1. The database has R data structures. The computer system creates a plan, Plan 1, to execute Q1. Plan 1 includes the identification of a first set of data structures, P1, to be accessed in executing Plan 1, at the current timestamp of time TT1. The number of data structures in P1 is less than R. The computer system determines that Plan 1 is valid for a period of time. The computer system stores in a cache Plan 1 and an indication that Plan 1 is valid for the period of time.