Virtual Index for Partitioned Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing queries to partitioned database tables is inefficient due to the need for separate optimization of each partition, even when they share common characteristics, leading to increased overhead that negates the performance benefits of partitioning.
Innovation Solution
A query optimizer generates a virtual maintained temporary index that spans multiple partitions with common characteristics, allowing for a single access plan that reduces the need for individual optimization of each partition.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If separate optimization is performed for each partition, then query optimization accuracy is improved, but optimization workload and overhead increase significantly
Solution Approach 1:
The patent merges multiple individual maintained temporary indexes from different partitions into a single virtual maintained temporary index that spans multiple partitions. This consolidation allows the query optimizer to work with one unified index structure rather than separately optimizing against multiple individual indexes, thereby reducing optimization workload while maintaining the ability to accurately optimize queries across partitioned data.
Solution Approach 2:
The virtual maintained temporary index serves multiple partitions simultaneously, providing a universal indexing structure that can be used for optimizing queries across different partitions. This multi-functional index eliminates the need for separate optimization processes for each partition while still preserving the specific characteristics needed for accurate query optimization.
2Loss of time
If a single optimization strategy is applied to all partitions, then optimization workload is reduced, but optimization effectiveness decreases for partitions with different characteristics
Solution Approach 1:
The virtual maintained temporary index preserves local characteristics of individual partitions while providing a unified optimization interface. Each partition's specific data distribution and access patterns are maintained within the virtual index structure, allowing the optimizer to apply locally-appropriate optimization strategies without requiring separate optimization processes for each partition.
Data Source
AI summary
A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.