Database Partition Pruning Using Non-Leading Column Predicates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems have limited partition pruning capabilities, as they typically rely on matching predicates and cannot effectively utilize predicates on columns other than the leading column, leading to inefficient query processing and increased data scanning.
Innovation Solution
A query optimizer that utilizes predicates on non-leading columns and infers pseudo-partitions based on limit key values to prune partitions more effectively, allowing for more efficient data retrieval by generating bounded ranges and making pruning decisions at compile time, even with host variables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional query optimizers use only matching predicates on leading columns for partition pruning, then the implementation is simple, but the partition pruning capability is limited and query efficiency deteriorates
Solution Approach 1:
The patent changes the parameter of predicate types from only matching predicates to include both matching predicates and screening predicates. This allows the query optimizer to utilize predicates on any column of the index, not just leading columns, thereby improving partition pruning capability and query efficiency without fundamentally changing the optimizer's structure
Solution Approach 2:
The patent segments the predicate evaluation process into two distinct types: matching predicates (which must match the index column order) and screening predicates (which can apply to any column). This segmentation allows the system to handle different predicate types through different evaluation paths, improving overall efficiency while maintaining manageable complexity
2Productivity
If the query optimizer utilizes predicates on non-leading columns for partition pruning, then more partitions can be pruned, but the complexity of partition evaluation increases
Solution Approach 1:
The patent performs preliminary evaluation of screening predicates against partition boundaries before executing the full query. By checking whether predicate values fall within the bounded ranges of partition key values, the system can eliminate entire partitions in advance, reducing the number of partitions that need detailed evaluation and thus managing complexity while improving pruning effectiveness
Solution Approach 2:
The patent applies different evaluation strategies to different parts of the index structure. Matching predicates on leading columns receive optimized evaluation using index structure properties, while screening predicates on non-leading columns receive evaluation based on bounded range analysis. This localized approach to different predicate types manages overall complexity
3Productivity
If the system resolves host variables at runtime, then query flexibility is maintained, but query processing time increases
Solution Approach 1:
The patent performs preliminary partition pruning at compile time using host variable symbols without resolving their actual values. The query optimizer can determine which partitions to prune based on the structure of the query and index, even when specific variable values are unknown. This preliminary action speeds up query processing while maintaining flexibility, as the actual variable values are only needed for the reduced set of partitions
Data Source
AI summary
A system for executing a query on data that has been partitioned into a plurality of partitions is provided. The system includes providing partitioned data including one or more columns and the plurality of partitions. The partitioned data includes a limit key value associated with each column for a given partition. The system further includes receiving a query including a predicate on one of the one or more columns of the partitioned data; and utilizing the predicate on the one of the one or more columns in a pruning decision on at least one of the one or more partitions based on the limit key values associated with the plurality of partitions.