Indicates the size of a pool of numbers reserved by a vproc for assigning identity values to rows inserted into an identity column table.
Identity columns are used mainly to ensure row uniqueness by taking a system-generated unique value. They are valuable for generating simple unique indexes and primary and surrogate keys when composite indexes or keys are not desired. Identity columns are also useful for ensuring column uniqueness when merging several tables or to avoid significant preprocessing when loading and unloading tables. For more information on identity columns, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Field Group
General
Valid Range
1 through 1,000,000
Default
100,000
Changes Take Effect
After the DBS Control Record has been written.
Usage Notes
When the initial batch of rows for a bulk insert arrives on a PE/AMP vproc, the following occurs:
- A range of numbers is reserved before processing the rows.
- Each PE/AMP retrieves the next available value for the identity column from the IdCol table.
- Each PE/AMP immediately updates this value with an increment equal to the IdCol Batch Size setting.
Bulk loads using MultiLoad, FastLoad, and INSERT... SELECT, have identity values assigned by the AMPs. For these types of loads, base the IdCol Batch Size setting on the number of AMPs in the system.
Bulk loads using TPump and iterated inserts have identity values assigned by the PEs. For these types of loads, base the setting on the number of PEs in the system.
IdCol Batch Size Performance Implications
The IdCol Batch Size setting involves a trade-off between insert performance and potential gaps in the numbering of rows inserted into tables that have identity columns.
A larger setting results in fewer updates to DBC.IdCol in reserving batches of numbers for a load. This can improve the performance of bulk inserts into an identity column table. However, because the reserved numbers are kept in memory, unused numbers will be lost if a database restart occurs, resulting in a gap in the numbering of identity columns.