Incremental Planning and Execution (IPE) is a framework for planning and executing query requests in smaller incremental request fragments. Spool data, called results feedback, or statistics on the spool data, called statistics feedback, are collected dynamically during the execution of a fragment and used by the Teradata query optimizer to determine the planning of the remaining fragments. The results feedback enables the Teradata query optimizer to apply advanced query optimizations such as partition elimination, join elimination, join index rewriting, and predicate simplification. The optimizer uses the statistics feedback to provide accurate cost estimation. With this release, IPE adds enhancements to dynamic feedback for increased efficiency.
Benefits
- Statistics feedback from spools to provide input to the aggregation steps or analytical-function steps. The optimizer uses the feedback to formulate an optimal plan for those steps.
- Statistics feedback from materialized views, derived tables, and subqueries in addition to table operators. The optimizer uses this feedback to choose an optimal plan for joins with views, derived tables, and subqueries.
- Dynamic statistics collection from samples. The size of a sample, that is, the number of rows in the sample, is determined dynamically based on the characteristics of underlying data. The sampling-based statistics collection minimizes system resource usage in collecting statistics on spools.
- Results feedback enables more simplifications and optimizations such as partition elimination, new access path selection, and join index rewrites to improve query performance. Results feedback is provided for the following additional cases:
- Noncorrelated EXISTS or NOT EXISTS subqueries in a WHERE clause.
- Noncorrelated single-column IN, NOT IN, ANY, or ALL subqueries in a WHERE clause.
- Single-row spools: derived tables or views for which the result is determined to be one row.
- Dynamic feedback kind switching, in which AMPs choose the feedback kind based on the actual execution and result size, regardless of the feedback kind requested by the optimizer. Switching from results feedback to stats feedback reduces processing loads on the optimizer to minimize parsing time. When a target spool is empty or contains only one row, switching from statistics feedback to results feedback enables complex queries to benefit from results feedback.
- Enhanced release-spool logic, which automatically detects and removes obsolete spools. A spool becomes obsolete when the spool is left for future use by subsequent fragments, but becomes unnecessary after the query rewrite. The release-spool logic prevents spools from being left after query execution is complete and reduces peak spool usage during query processing.
Considerations
- IPE is enabled by default.
- The optimizer uses various criteria to automatically determine when to generate a dynamic plan.
- You can use query bands to indicate when the optimizer uses a dynamic plan.
- Recursive queries are not eligible for IPE.
- Multistatement requests are not eligible for IPE. This includes multistatement requests that are user specified or internally generated, such as triggers.
- Enhanced IPE is available with the Enterprise license tier.
Additional Information
- Teradata Vantage™ SQL Data Manipulation Language , B035-1146
- Teradata Vantage™ SQL Request and Transaction Processing, B035-1142