Multiple-statement 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
- The target table must be empty.
- All INSERT statements in the multiple-statement request must have the same target table.
- Only INSERT statements can be included in the request.
If you insert other statement types into the multiple-statement request, the fast path optimization does not occur (only the first INSERT ... SELECT in the series is optimized) and performance degrades accordingly.