Query Optimizer Delaying Expensive Expression Evaluation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The early evaluation technique for query processing is not optimal when dealing with expensive expressions, as it evaluates these expressions for every row in a table, leading to high costs and inefficiencies, especially when the expressions are non-selective or appear in the SELECT list, resulting in suboptimal query execution.
Innovation Solution
The method involves delaying the evaluation of expensive expressions relative to operations that do not evaluate them, by generating equivalent execution plans that push down predicates and optimizing parallel processing, thereby reducing resource usage and improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If early evaluation technique is used to evaluate filter predicates as early as possible, then the amount of data processed in later operations is reduced, but the cost of evaluating expensive expressions for every row becomes exorbitantly high
Solution Approach 1:
The patent identifies and evaluates cheap predicates first before evaluating expensive expressions. This preliminary action filters out rows that cannot possibly satisfy the query conditions, thereby reducing the number of rows that require expensive expression evaluation and improving overall query efficiency
Solution Approach 2:
The query optimizer automatically identifies expensive expressions and rearranges the evaluation order without external intervention. The system serves itself by detecting which predicates are expensive and which are cheap, then autonomously optimizing the execution plan to evaluate cheap predicates first
2Reliability
If expensive expressions are evaluated for every row in a large table, then complete accuracy is achieved, but the resource consumption and execution time become unacceptably high
Solution Approach 1:
The patent applies preliminary filtering using cheap predicates before evaluating expensive expressions. This ensures that only rows with potential to satisfy the query conditions proceed to expensive expression evaluation, maintaining result accuracy while dramatically reducing execution time for large tables
Solution Approach 2:
The patent evaluates expensive expressions only for the subset of rows that pass the cheap predicate filter, rather than for all rows. This partial action is sufficient to achieve correct results while avoiding the excessive time cost of universal evaluation
3Productivity
If expensive expressions appear in the SELECT list rather than predicates, then the early evaluation technique still evaluates them for every row, but the benefit of row reduction is insufficient to compensate for the high evaluation cost
Solution Approach 1:
The patent evaluates cheap predicates in the WHERE clause before evaluating expensive expressions in the SELECT list. This preliminary action reduces the number of rows that reach the SELECT list evaluation stage, making the overall process more efficient even when expensive expressions appear in the projection
Data Source
AI summary
Techniques are provided for delaying evaluation of expensive expressions in a query. Expensive expressions in the query are established by cost information or by looking up a list of known expensive expressions for a match. After an execution plan is determined by using the early evaluation technique, one or more equivalent execution plans is established. The one or more equivalent execution plans may include both a type of execution plans that delay evaluation of expensive expressions and a type of execution plans that do not. In addition, the one or more equivalent execution plans may include both parallelized and non-parallelized alternatives to the execution plan identified by the early evaluation technique. Finally, based on a set of criteria, which may include comparing cost information among all the equivalent execution plans generated thus far, the best execution plan is chosen for the query.