Hybrid Query Optimization for Partitioned Database Access Paths
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional query optimizers use a 'one size fits all' approach, which can lead to inefficient execution plans for queries accessing partitioned databases, as they do not differentiate between indexed and non-indexed partitions, resulting in suboptimal performance.
Innovation Solution
A hybrid query optimization approach that allows different subsets of data to be optimized with distinct access paths, join orders, and execution methods by re-writing queries using 'table expansion' transformations, enabling the use of indexes where applicable and sequential scans for non-indexed partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a conventional optimizer uses a one size fits all approach to determine a single access path for a query, then the execution plan is simple to generate, but the query execution efficiency deteriorates when the table is partitioned with mixed indexed and non-indexed partitions
Solution Approach 1:
The patent segments the query execution plan into multiple access paths, one for each partition of the table. Instead of using a single uniform access path for all partitions, the system generates separate access paths tailored to the specific characteristics of each partition (indexed vs. non-indexed), thereby optimizing query execution efficiency without requiring excessive manual complexity in plan generation
Solution Approach 2:
The patent introduces dynamic adaptation in the execution plan generation process. The system automatically adjusts the access path selection based on the detected characteristics of each partition (whether it has an index or not). This dynamic behavior allows the optimizer to adapt to mixed partition scenarios without manual intervention, resolving the contradiction between efficiency and complexity
2Productivity
If the optimizer selects a sequential scan approach to execute the entirety of a query on a partitioned table, then the execution plan is simple and uniform, but performance deteriorates for indexed partitions that could benefit from index-based access
Solution Approach 1:
The patent applies local quality by making the access path selection specific to each partition rather than uniform across all partitions. Indexed partitions receive index-based access paths optimized for their structure, while non-indexed partitions use sequential scans. This localized optimization eliminates wasted time on full table scans for indexed partitions while maintaining simplicity through automated detection and assignment
Solution Approach 2:
The system changes the access path parameter dynamically based on partition characteristics. For each partition, the optimizer evaluates whether an index-based access path or sequential scan is more appropriate and selects accordingly. This parameter adaptation resolves the contradiction by preventing time waste on inappropriate full table scans while maintaining execution plan simplicity through automated decision-making
3Productivity
If the optimizer uses an index-based access path for indexed partitions, then query execution is optimized for those partitions, but the one size fits all approach forces the same access path to be used for non-indexed partitions
Solution Approach 1:
The patent implements dynamic adaptability in the execution plan generation process. The system automatically detects the characteristics of each partition (indexed or non-indexed) and adapts the access path selection accordingly. This dynamic behavior provides versatility to handle different partition types effectively while maintaining execution efficiency, resolving the contradiction between optimized performance and adaptability
Solution Approach 2:
The execution plan generation system achieves multi-functionality by being capable of generating both index-based access paths and sequential scan access paths within a single unified process. The optimizer universally handles different partition types (indexed and non-indexed) using the same automated logic, providing adaptability to various scenarios while maintaining efficiency through appropriate access path selection for each partition type
Data Source
AI summary
An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.