Fast Path Insert and Delete Statements.

Rules for Fast Path Insert and Delete Statements

Solution/source: Knowledge article

Tech Note

Unconstrained (Fast Path) DELETE Processing
When submitted by itself as a transaction, an unconstrained delete
(DELETE...ALL) is processed very quickly.
An unconstrained DELETE is one where no conditions are attached, that is, all
rows are deleted. When an unconstrained delete is submitted as part of a
multi statement transaction, the speed with which it is processed depends on its
position in the transaction.
To enable fast path processing, which defers processing until a transaction ends
and allows rollback processing to be almost instantaneous, positioning must be
as follows, depending on the mode being used.

In Teradata Mode:
FOR this type of transaction Positioning must be as follows
Implicit (such as a multistatement macro or BTEQ request) DELETE...ALL must be the last statement in the request.
DELETE...ALL must be immediately followed by the END TRANSACTION
statement that terminates the currently open transaction.
Explicit This implementation is not available to a Teradata embedded SQL application
because the DELETE and END TRANSACTION statements must fall into the
same request packet.
======================================================================
Fast Path INSERT … SELECT Statements
Multistatement INSERT … SELECTs are optimized so that each statement
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 statement 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 cannot 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.

Rules for Fast Path INSERT … SELECT Statements
There are several restrictions that must be observed in order for the high
performance fast path optimization to succeed in a multistatement request.
These rules are as follows:
• 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.