Correlation Mapping for Cloud Query Partition Elimination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques for cloud-based stores fail to optimize queries when predicates are on non-partitioning columns, leading to inefficient execution and data retrieval.
Innovation Solution
Leverage correlations between data columns and partitioning attributes in cloud-based stores using formula-based or lookup-based mappings to enhance query optimization, allowing for early row elimination and reduced data transfer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing query optimization techniques are used that leverage only partitioning columns, then queries with predicates on partitioning columns can be optimized, but queries with predicates on non-partitioning columns cannot be optimized leading to full data scans
Solution Approach 1:
The patent introduces correlation information as an intermediary between non-partitioning columns and partitioning columns. This correlation data acts as a mediator that enables the query optimizer to translate predicates on non-partitioning columns into equivalent predicates on partitioning columns, thereby enabling optimization for a broader range of queries without changing the fundamental partitioning structure
Solution Approach 2:
The patent adds a new dimension to query optimization by incorporating correlation metadata that maps relationships between non-partitioning columns and partitioning columns. This additional dimensional information allows the optimizer to reason about data distribution beyond the explicit partitioning columns, enabling optimization of queries that previously could not be optimized
2Quantity of substance
If cloud-based stores store massive data files, then data capacity is increased, but accessing and retrieving relevant segments becomes inefficient without proper optimization
Solution Approach 1:
The patent performs preliminary action by pre-computing and storing correlation information between non-partitioning columns and partitioning columns. This advance preparation of correlation metadata enables the query optimizer to quickly determine which data segments are relevant without having to scan entire files, thus maintaining fast retrieval speeds even as data capacity grows
Solution Approach 2:
The patent extracts only the relevant data segments needed to answer a query by using correlation information to identify and retrieve only the necessary partitions. Instead of accessing entire massive files, the system extracts and retrieves only the specific segments that contain data matching the query predicates, significantly improving retrieval speed
Data Source
AI summary
A method and apparatus for optimizing a query in a relational database management system (RDBMS) when a predicate on a data column in the query has a correlation to a partitioning attribute of a partitioning column in data retrieved from a cloud-based store, wherein the optimizing uses the correlation between the data column in the query to the partitioning column in the data retrieved from the cloud-based store for data elimination when processing the query. The correlation is defined in a formula or lookup data structure that maps or range-maps from the data column to the partitioning column.