WorkDBSize - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
xha1591998860283.ditamap
dita:ditavalPath
xha1591998860283.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantageā„¢
Specifies the maximum size for multirow data blocks in work tables. Rows that are larger than WorkDBSize are stored in single-row data blocks, which are not limited by WorkDBSize.

Field Group

File System

Valid Range

42 through 2047 sectors for systems that use large cylinders.

18 through 512 sectors for systems that use small cylinders.

A sector is 512 bytes.

Default

254 sectors

Setting this field to 0 causes Vantage to use the system default size for this type of data block.

Changes Take Effect

After a system initialization (sysinit) or an upgrade to a major xx.00 release.

Usage Notes

When database tables are initially populated, Vantage stores as many rows as possible into each data block, until the block reaches the size specified by the various DB size settings in DBS Control. As tables are subsequently modified, rows can grow such that the existing data blocks would exceed the maximum size. When this happens, the data block is split, and roughly half the rows are moved to a new data block, with the result that the original and new data blocks are each one half of the original size. The result of this type of growth and splitting is that data blocks for heavily modified tables tend to be about 75% of the maximum size defined in DBS Control.

Systems initialized on Teradata Database 13.10 or later release use large cylinders. On large-cylinder systems, the minimum data block size is 42 sectors (21504 bytes). The minimum data block size accepted by the CREATE TABLE and ALTER TABLE SQL statements is 21248 bytes (41.5 sectors). Tables created or altered to use this minimum size will have the value rounded up by the parser to 42 sectors, so these tables will have an actual minimum data block size of 21504 bytes.

Systems initialized on a Teradata Database release prior to 13.10 and subsequently upgraded without a sysinit use small cylinders. On small-cylinder systems, the minimum data block size is 18 or 19 sectors (9216 bytes or 9728 bytes). The minimum data block size accepted by the CREATE TABLE and ALTER TABLE statements is 8960 bytes (17.5 sectors). Tables created or altered to use this minimum size will have the value rounded up by the parser to 18 sectors, so these tables will have an actual minimum data block size of 9216 bytes.

If DBs are compressed, this setting applies to the size of the uncompressed DBs.