Identity column values for rows bulk-inserted into a table defined with a GENERATED ALWAYS identity column are replaced automatically with system-generated numbers. A warning of this replacement is issued only once for all rows inserted using an INSERT ... SELECT statement. Other bulk-inserting utilities such as Teradata Parallel Data Pump and BTEQ imports receive the same warning for each row inserted.
- GENERATED BY DEFAULT
- GENERATED ALWAYS with CYCLE
Optimizing Bulk Inserts into an Identity Column
The performance of bulk INSERT operations into a table with an identity column depends on the system setting for the IdCol batch size parameter. This variable optimizes the tradeoff between bulk insert performance and your tolerance for the identity column numbering gaps that can occur with a system restart. The performance enhancements realized by larger batch size are due to the decrease or elimination of updates to DBC.IdCol that must be performed to reserve identity column numbers for a load.
A larger IdCol batch size setting enhances bulk insert performance, but also means that more identity column numbers are lost if a system restart occurs. Reserved identity column numbers are memory-resident. If the database must be restarted, the batch insert transaction is rolled back, and those reserved identity column numbers are lost.
The optimal batch size for your system is a function of the number of AMPs. The IdCol batch size default value of 100,000 rows is sufficient for most workloads, but perform your own tests to determine what works best for your applications and system configuration.
When you are bulk inserting rows into an identity column table during a Teradata mode session, be careful not to upgrade the default lock severity of a SELECT operation on DBC.IdCol from ACCESS to READ or higher severity if the SELECT is performed within the boundaries of an explicit transaction because you run the risk of creating a deadlock on the IdCol table.