Database Query Optimization via Partition Elimination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database queries for large partitioned databases are inefficient as they require processing all partitions, leading to unnecessary computational overhead.
Innovation Solution
The method involves determining a partition list based on the query, excluding unnecessary partitions, and generating sub-queries only for the included partitions, allowing for optimized processing and reduced computational load.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all partitions are queried to retrieve data from a partitioned database table, then complete data coverage is ensured, but processing time and computational overhead increase
Solution Approach 1:
The patent segments the query processing task by dividing it into partition-level sub-queries. Instead of treating the entire partitioned table as a single unit, the system breaks down the query into smaller, independent operations on individual partitions, allowing for selective execution and parallel processing where applicable.
Solution Approach 2:
The patent extracts and eliminates unnecessary partitions from the query execution plan. By analyzing the query's filter conditions against partition definitions, the system identifies and excludes partitions that cannot contain relevant results, processing only the necessary subset of partitions to answer the query.
2Reliability
If all partitions are processed for every query, then no relevant data is missed, but computational overhead and resource consumption increase
Solution Approach 1:
The patent performs preliminary analysis of query conditions against partition metadata before executing the actual query. By pre-evaluating which partitions could potentially contain relevant data based on filter conditions, the system prepares an optimized execution plan that avoids unnecessary partition processing, thereby reducing computational overhead while ensuring data completeness.
3Productivity
If partition filtering is applied to reduce processing scope, then query performance improves, but complexity of query optimization increases
Solution Approach 1:
The patent implements self-service query optimization where the database system automatically performs partition elimination without requiring manual intervention. The query optimizer automatically analyzes filter conditions, compares them with partition definitions, and generates an optimized execution plan that processes only necessary partitions, thereby improving query performance while keeping the optimization process transparent to users.
Data Source
AI summary
A computer-based method for optimizing database queries is provided. The method uses a server computer device including a processor communicatively coupled to a database. The method includes receiving a main query requesting data from a partitioned database table within the database. The partitioned database table includes a plurality of partitions. The method also includes determining a partition list. The partition list includes two or more partitions of the plurality of partitions and excludes at least one partition of the plurality of partitions. The method further includes generating a plurality of sub-queries where each sub-query is associated with one partition included within the partition list, and processing each sub-query of the plurality of sub-queries on the associated partition included within the partition list.