Optimizing Bulk Inserts Into an Identity Column - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

The performance of bulk INSERT operations into a table with an identity column depends on the DBS Control setting for the IdCol batch size parameter (see Teradata Vantage™ - Database Utilities, B035-1102 for information about DBS Control). You should use this variable to optimize the trade off 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.

While a larger IdCol batch size setting enhances bulk insert performance, it 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 permanently 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 should be sufficient for most workloads, but you should 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.