DATABLOCKSIZE Option | CREATE TABLE (Table Options Clause) | Teradata Vantage - DATABLOCKSIZE - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the file system.

Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed.

A row that is larger than the DATABLOCKSIZE setting is stored in its own data block. Vantage does not split rows across data blocks.

A sector is normally 4 KB. If the computed value for the DATABLOCKSIZE attribute does not fall within the allowable range, the system returns an error message to the requestor.

If you do not specify a value for DATABLOCKSIZE, then the database uses the system-wide default data block size specified by the PermDBSize field in the DBS Control record. The default value is typically 254 sectors and the maximum is 255 sectors for all systems. For information about the DBS Control record, see Teradata Vantage™ - Database Utilities, B035-1102.

The default DATABLOCKSIZE settings depend on the cylinder size that is set for your system.

Following are release-specific rules for minimum data block sizes.

The DATABLOCKSIZE value returned in response to a SHOW TABLE statement is either the value specified in the CREATE TABLE statement for the table or the value specified in the most recently entered ALTER TABLE statement.

For additional information about the data block size, see:
  • Teradata Vantage™ - Database Administration, B035-1093
  • Teradata Vantage™ - Database Design, B035-1094
  • Teradata Vantage™ - Database Utilities, B035-1102

Large-Cylinder Systems

Systems initialized on Teradata Database 13.10 or later release use large cylinders. For large-cylinder systems, the following tables list the minimum and maximum data block sizes.

Minimum Data Block Size Description
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.
Maximum Data Block Size Description
2047 sectors (1048064 bytes) The maximum data block size accepted by the CREATE TABLE and ALTER TABLE SQL statements is 1048319 bytes (2047.5 sectors). Tables created or altered to use this maximum size will have the value rounded down by the parser to 2047 sectors, so these tables will have an actual maximum data block size of 1048064 bytes.

Small-Cylinder Systems

Systems initialized on a Teradata Database release prior to 13.10 and subsequently upgraded without running the Sysinit utility use small cylinders. For small-cylinder systems, the following tables list the minimum and maximum data block sizes.

Minimum Data Block Size Description
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.
Maximum Data Block Size Description
512 sectors (262144 bytes) The maximum data block size accepted by the CREATE TABLE and ALTER TABLE SQL statements is 262399 bytes (512.5 sectors). Tables created or altered to use this maximum size will have the value rounded down by the parser to 512 sectors, so these tables will have an actual maximum data block size of 262144 bytes.