Query Subquery Elimination via Predicate Replacement
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query processing technologies face inefficiencies when dealing with queries containing subqueries, leading to excessive resource consumption and delayed result sets due to the need to load and process multiple instances of data objects, especially for medium to large data sets.
Innovation Solution
Transforming queries by eliminating subqueries and replacing them with added predicates that are semantically equivalent, allowing the query processor to avoid loading separate instances of data objects and reducing resource consumption by processing only a single instance of the data object referenced in the outer query.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If subqueries are processed by loading separate instances of data objects, then query completeness is maintained, but resource consumption increases and execution time delays
Solution Approach 1:
The patent merges multiple instances of the same data object into a single instance by eliminating subqueries and replacing them with predicates that reference the outer query's data object instance. This consolidation prevents redundant loading and processing of duplicate data, reducing resource consumption while maintaining query correctness.
Solution Approach 2:
The patent extracts and eliminates the subquery structure from the query plan, removing the separate data object instance creation that causes resource waste. By taking out the subquery and replacing it with a predicate referencing the outer query's instance, the harmful redundant processing is eliminated.
2Measurement precision
If subqueries are processed by loading separate instances of data objects, then query accuracy is maintained, but execution time increases
Solution Approach 1:
The patent combines multiple data object instances into one by eliminating subqueries. The single instance is processed once and reused through predicate references, dramatically reducing execution time from hours to seconds for large datasets while preserving query accuracy through semantic equivalence.
3Reliability
If multiple instances of data objects are loaded for subquery processing, then query correctness is ensured, but processing efficiency decreases
Solution Approach 1:
The patent extracts and removes the inefficient subquery mechanism that creates multiple data object instances. By replacing subqueries with predicates referencing the outer query's single instance, the system eliminates redundant processing overhead and improves productivity.
Solution Approach 2:
The patent changes the query structure parameter from a subquery-based approach to a predicate-based approach. This parameter change transforms the processing model from loading multiple instances to reusing a single instance, significantly improving processing efficiency.
Data Source
AI summary
A method, apparatus, and stored instructions are provided for transforming an initial query by eliminating a subquery nested within the initial query. The initial query may include an outer query that references a first instance of a particular data object. The initial query may also include set operator(s). At least a particular set operator may include a particular subquery that references a different instance of the particular data object. A query processor may transform the initial query to a transformed query that excludes the particular set operator and the particular subquery. The transformed query may instead include an added predicate that is based at least in part on the particular subquery. The added predicate may reference the first instance of the particular data object without referencing the different instance of the particular data object. The transformed query may be used for query execution instead of the initial query.