Partition Pruning via Query Rewrite Using Global Domain Index
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query processing techniques on partitioned databases incur significant overhead by accessing each index partition, even when only a subset of partitions contains relevant data, leading to inefficient processing and increased costs.
Innovation Solution
The query is rewritten to access only the index partitions that contain the requested data by using a global domain index, eliminating the need to access partitions without matching data, thereby reducing processing time and cost through partition pruning logic that identifies and selects relevant partitions using metadata and row identifiers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a query plan accesses each index partition to determine data relevance, then the query can be executed on all partitions, but significant processing overhead is incurred for partitions without relevant data
Solution Approach 1:
The patent extracts and utilizes partition metadata (minimum bounding rectangles) separately from the actual data partitions. By accessing metadata first to identify relevant partitions, the system extracts only the necessary partition information and excludes irrelevant partitions from query processing, thereby reducing processing overhead while maintaining query completeness
Solution Approach 2:
The patent performs preliminary action by accessing partition metadata and determining data relevance before executing the query on actual partitions. This preliminary metadata access and partition identification step prevents unnecessary query execution on partitions without relevant data, reducing overall processing overhead
2Loss of time
If partition metadata is accessed for each partition to determine data relevance, then execution time is reduced, but overhead is still incurred for accessing each index partition
Solution Approach 1:
The patent introduces partition metadata as an intermediary layer between the query and the actual data partitions. The metadata contains minimum bounding rectangles that serve as a filtering mechanism, allowing the system to quickly determine partition relevance without directly accessing each data partition, thus reducing both execution time and overhead
3Measurement precision
If MBR intersection comparison is performed for each partition, then spatial query accuracy is maintained, but processing cost increases due to accessing every partition
Solution Approach 1:
The patent extracts the essential spatial filtering function by using minimum bounding rectangles from metadata to identify relevant partitions. By taking out only the necessary partition identification step using MBR comparison, the system maintains spatial query accuracy while avoiding unnecessary processing on irrelevant partitions, thereby improving overall processing efficiency
Data Source
AI summary
Systems, methods, and other embodiments associated with query pruning via query rewrite are described. One example method includes receiving a query on a partitioned table that will result in a first query plan that includes accessing a local domain index on each partition in the database. The query is rewritten to generate a second query that will result in a second query plan that includes accessing a global domain index instead of the local domain index on each partition in the database.