17.05 - Fast Path INSERT … SELECT Requests - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Multistatement INSERT … SELECTs are optimized so that each request except for the last in the series returns a ‘zero row inserted’ message as a response. The retrieved rows for each SELECT are sent to an empty target table. The last INSERT … SELECT request returns the total number of rows inserted for the entire request and sorts and merges the spool table into the target table.

Columns defined with the COMPRESS option do not participate in fast path optimizations, so if you perform an INSERT … SELECT operation on compressed columns, fast path optimization is not specified by the Optimizer when it creates an access plan.

INSERT … SELECT Performance and Target Table Identity Column Primary Indexes

For those cases where INSERT … SELECT is optimized so that a direct merge from source to target table is possible, like when source and target tables have the identical structure, there can be as much as a threefold degradation of performance caused by the need for an extra step to spool and redistribute rows when the target table has an identity column primary index.

Rules for Fast Path INSERT … SELECT Requests

Observe the following restrictions for the high performance fast path optimization in a multistatement request:
  • The target table must be empty.
  • All INSERT statements in the multistatement request must have the same target table.
  • Only INSERT statements can be included in the request.

If you insert other statement types into the multistatement request, the fast path optimization does not occur (only the first INSERT … SELECT in the series is optimized) and performance degrades accordingly.