Persistent Hint Table for Query Plan Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimization methods often produce suboptimal query plans due to global application of rules, failing to provide the most efficient execution strategy for specific queries in multi-level database management systems.
Innovation Solution
Associating queries with hints that modify the execution plan, allowing an optimizer to compile a second query plan which can be stored and reused, potentially replacing the initial plan in the cache, thereby improving execution time and state preservation during system restarts or upgrades.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If global rules are applied to generate query plans, then the system maintains simplicity in rule application, but query execution time increases due to suboptimal plans
Solution Approach 1:
The patent segments the query optimization process by creating separate hint tables for different queries, allowing individualized optimization strategies rather than applying a single global rule set. Each hint table stores specific optimization hints for particular queries, enabling targeted improvements without complicating the overall system architecture.
Solution Approach 2:
The patent implements preliminary action by pre-computing and storing optimized query plans in hint tables before actual query execution. When a query is executed, the system retrieves pre-prepared hints that have already performed optimization work, eliminating the need for complex real-time optimization during query processing.
2Productivity
If query plans are cached without hints, then the system maintains simplicity in cache management, but query execution time increases due to lack of optimization
Solution Approach 1:
The patent introduces hint tables as an intermediary layer between the query cache and the query execution engine. These hint tables store optimization information that mediates between the cached query plans and the actual execution, providing optimized guidance without requiring complex modifications to the cache management system itself.
Solution Approach 2:
The patent creates copies of query plans with embedded hints stored in hint tables. Instead of modifying the original cached query plans, the system generates and stores separate hinted versions that can be retrieved and applied during execution, maintaining cache simplicity while enabling optimization.
3Ease of manufacture
If the system restarts or performs binary upgrades, then system maintenance is enabled, but query plan state is lost requiring recompilation
Solution Approach 1:
The patent extracts query optimization hints from the volatile memory and stores them in persistent hint tables on disk. This separation allows the hints to be preserved independently of the database server's runtime state, enabling retrieval after restarts or upgrades without requiring recompilation of query plans.
Solution Approach 2:
The patent changes the persistence parameter of query hints from volatile (memory-only) to persistent (disk-stored). By storing hints in a persistent format that survives system restarts and binary upgrades, the system maintains optimization state across maintenance operations, eliminating recompilation requirements.
4Productivity
If optimized query plans with hints are stored in cache, then query execution time decreases, but memory usage increases
Solution Approach 1:
The patent moves hint storage from the primary memory dimension to a secondary persistent storage dimension. By storing hints in disk-based hint tables rather than exclusively in memory, the system provides optimized query execution while managing memory consumption, as hints are loaded on-demand rather than held entirely in RAM.
Data Source
AI summary
In one aspect there is a method. The method may include associating a query received from an application with a hint, wherein the hint modifies an execution of the query. The method may further include an optimizer to compile the query according to the hint to produce a second query plan. When no hint is associated with the query, the optimizer may produce a first query plan. The method may further include storing the hint in a persistent table to enable state preservation during a database restart or binary upgrade, and/or executing the query using the second query plan.