Query Processing for Minus and Intersect Operators
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in processing queries that contain MINUS and INTERSECT operators, particularly in large data sets, as current strategies like sort-merge joins and anti-joins do not scale effectively.
Innovation Solution
The approach involves transforming queries with MINUS and INTERSECT operators into equivalent queries using equijoins, anti-joins, and semi-joins, along with duplicate elimination operations, and estimating costs to select the most efficient execution plan, allowing for scalability and parallelization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If sort-merge joins and anti-joins are used to process MINUS and INTERSECT operators, then the operations can be performed on multi-set sources, but the approach does not scale effectively for large data sets
Solution Approach 1:
The patent transforms the query operators by changing their fundamental parameters - converting MINUS and INTERSECT operations into equivalent queries using equijoins, anti-joins, and semi-joins with duplicate elimination. This parameter transformation enables the use of more efficient join algorithms that scale better with large data sets, directly addressing the productivity vs. complexity contradiction by improving processing efficiency through operational transformation
2Ease of operation
If traditional MINUS and INTERSECT operations are used, then the query logic is straightforward, but the processing time and resource consumption increase for large data sets
Solution Approach 1:
The patent applies preliminary action by performing duplicate elimination operations at intermediate stages rather than waiting for final result processing. By eliminating duplicates earlier in the query execution plan through semi-joins and anti-joins, the system reduces the data volume that subsequent operations must process, thereby reducing overall processing time while maintaining query simplicity through automated transformation
3Productivity
If queries are optimized for specific data set sizes, then performance improves for those cases, but the solution lacks adaptability for varying data volumes
Solution Approach 1:
The patent implements dynamics by creating adaptive query execution plans that automatically adjust to varying data set sizes. The transformation into equijoins, anti-joins, and semi-joins with duplicate elimination creates a flexible framework that scales dynamically - the same transformed query structure efficiently handles both small and large data sets, providing both improved performance and scalability through dynamic adaptability
Data Source
AI summary
Various techniques are described for processing database commands that include MINUS and/or INTERSECT operators. The queries containing the MINUS and/or INTERSECT operators are transformed to create a plurality of transformed queries. Each of the transformed queries produces the same result as the original query, but does not include the MINUS and/or INTERSECT operator. To achieve the same result set as the original query, the transformed queries employ equijoins, antijoins, and/or semijoins, and duplicate elimination operations. Costs are estimated for each of the various transformed queries. Based on the cost estimates, one of the transformed queries is selected as the query that is to be executed to perform the operations specified in the original query.