Fast Path INSERT … SELECT Requests - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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.