Dynamic Partition Elimination in Multilevel Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face inefficiencies in executing complex queries over large volumes of data, particularly when multilevel partitioning is required, as existing methods struggle to dynamically eliminate unnecessary partitions, leading to increased data processing and query performance issues.
Innovation Solution
A database system employing both static and dynamic partition elimination techniques, where each partition can be subpartitioned, uses a parsing engine to distribute and organize data based on primary and partitioning columns, allowing for efficient query execution by identifying and excluding irrelevant partitions, and employing a multilevel partitioned primary index to optimize data retrieval and storage across multiple data-storage facilities.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If multilevel partitioning is used to organize data by year, month, and geographic region, then data organization and storage efficiency are improved, but query execution time increases because unnecessary partitions cannot be dynamically eliminated
Solution Approach 1:
The patent implements dynamic partition elimination by evaluating partition predicates at query execution time. The query optimizer dynamically determines which partitions to access based on actual query conditions, rather than using static elimination. This allows the system to adaptively navigate the multilevel partition structure (year-month-region) and eliminate irrelevant partitions dynamically, resolving the contradiction between maintaining organized storage and achieving fast query execution.
2Speed
If static partition elimination is used to eliminate partitions prior to query execution, then query processing speed is improved, but adaptability decreases when partitions cannot be determined statically
Solution Approach 1:
The system transitions from static to dynamic partition elimination by evaluating partition predicates during query execution. The query optimizer dynamically assesses which partitions satisfy query conditions and eliminates unnecessary ones on-the-fly. This dynamic approach maintains high query processing speed while providing the flexibility to adapt to various query types and partition structures, including multilevel partitions that cannot be statically determined.
3Reliability
If all partitions are scanned to ensure complete query results, then query accuracy is improved, but data processing volume increases significantly
Solution Approach 1:
The patent extracts and evaluates partition predicates from query conditions to identify and eliminate irrelevant partitions before scanning. By taking out the partition evaluation step and performing it dynamically, the system extracts only the necessary partitions that contain relevant data. This reduces the volume of data that needs to be processed while ensuring that all partitions containing query-relevant data are included, maintaining query accuracy without unnecessary data processing.
4Productivity
If multilevel partitioning with subpartitions is implemented, then storage efficiency and data organization are improved, but system complexity increases making dynamic partition elimination difficult
Solution Approach 1:
The patent segments the partition evaluation process into distinct levels corresponding to the multilevel partition structure (year, month, region). The query optimizer evaluates partition predicates at each level separately, navigating through the hierarchical partition structure systematically. This segmentation approach manages the complexity of multilevel partitioning by breaking down the evaluation process into manageable steps, allowing dynamic partition elimination to work effectively across multiple partition levels without overwhelming system complexity.
Data Source
AI summary
A method of searching a multilevel partitioned database includes receiving a query data from the multilevel partitioned database. At least for one level partitions are dynamically included. For some levels, partitions may also be statically included for execution of the query. The query is the executed over the partitions that are both dynamically and statically included. In one example, the cost of joining two tables in a multilevel partitioned database includes determining level partitions that can be statically included, estimating level partitions that will be dynamically considered for the join, and determining a cost as a function of the estimated statically included level partitions and estimated dynamically included level partitions.