Incremental Planning and Execution (IPE) is an adaptive query optimization framework in which the query optimizer can partially plan and execute a request incrementally. Vantage dynamically collects the intermediate results or statistics from each partial execution. The optimizer uses results feedback to rewrite the remaining portion of the request; this enables advanced optimizations such as partition elimination, join elimination, join index rewrite, and predicate simplification. The optimizer uses statistics feedback to produce a more accurate cost estimate, which is used to choose an optimal execution plan.
The following are some types of dynamic feedback in the two categories.
- Single-row relations: base relations with equality predicates on UPI or USI.
- Noncorrelated scalar subqueries.
- Noncorrelated EXISTS or NOT EXISTS subqueries in a WHERE clause.
- Noncorrelated single-column IN, NOT IN, ANY, or ALL subquery in a WHERE clause.
- Single-row spools: nonfolded derived tables or views for which the result is determined to be one row.
- Nonfolded derived tables or views.
- Remote tables, table operators, and table functions.
- Intermediate spools input to aggregations or analytic functions.
- For the original results feedback, if the size of the target spool is too big, switching from results feedback to statistics feedback reduces processing loads on the optimizer to minimize parsing time.
For the original statistics feedback, if the target spool is empty or contains only one row, switching from statistics feedback to results feedback enables complex queries to benefit from results feedback.
Example: IPE results feedback for a noncorrelated scalar subquery
First the optimizer detects the noncorrelated scalar subquery (bolded in the example).
SELECT * FROM t1, (SELECT t2.a2 FROM t2, t3 WHERE t2.a2 = t3.a3 GROUP BY t2.a2) AS dt (a2) WHERE t1.a1 < (SELECT MAX(t4.a4) FROM t4) AND t1.a1 = dt.a2;
Then the optimizer forms a fragment to execute the scalar subquery. The result of the scalar subquery is fed back to the optimizer and the result is substituted for the scalar subquery.
- Using transitive closure to derive predicates.
SELECT * FROM t1, (SELECT t2.a2 FROM t2, t3 WHERE t2.a2 = t3.a3 GROUP BY t2.a2) AS dt (a2) WHERE t1.a1 < 20 AND t1.a1 = dt.a2 AND dt.a2 < 20;
- Pushing the derived predicate into a derived table.
SELECT * FROM t1, (SELECT t2.a2 FROM t2, t3 WHERE t2.a2 = t3.a3 AND t2.a2 < 20 GROUP BY t2.a2) AS dt (a2) WHERE t1.a1 < 20 AND t1.a1 = dt.a2 AND dt.a2 < 20;
- Using transitive closure to derive predicates inside the derived table.
SELECT * FROM t1, (SELECT t2.a2 FROM t2, t3 WHERE t2.a2 = t3.a3 AND t2.a2 < 20 AND t3.a3 < 20 GROUP BY t2.a2) AS dt (a2) WHERE t1.a1 < 20 AND t1.a1 = dt.a2 AND dt.a2 < 20;
Further advanced optimizations as discussed above, such as partition elimination, can then occur.