DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the Teradata 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. Teradata Database 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.
- 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 tables below 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 tables below 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. |