17.10 - IdCol Batch Size - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

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:

  1. A range of numbers is reserved before processing the rows.
  2. Each PE/AMP retrieves the next available value for the identity column from the IdCol table.
  3. 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.