IMMEDIATE 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™

The only reason to change the data block size for a table is to enhance performance. Repacking the data blocks of large tables is a time-consuming process, so specifying the IMMEDIATE option for large tables substantially increases the amount of time required for the ALTER TABLE request to complete.

Field upgrades of systems do not change the data block size from 64 KB to 127.5 KB until a block splits. To take immediate advantage of the performance enhancements offered by the 127.5 KB block size, you must force the data block size change directly.

To upgrade the data block size from 63.5 KB to 127.5 KB, perform one of the following ALTER TABLE requests on every table in every database.

    ALTER TABLE database_name.table_name, DEFAULT DATABLOCKSIZE
                IMMEDIATE;

    ALTER TABLE database_name.table_name, DATABLOCKSIZE = 127.5 KBYTES
                IMMEDIATE;

If you do not specify the IMMEDIATE keyword, the definition for DATABLOCKSIZE is set to 127.5 KB, but the size increase does not occur until rows are inserted into the newly defined table.

Because of the extra time required to process requests with the IMMEDIATE option, you should plan to convert your data block sizes during non-peak hours.

When an ALTER TABLE request that specifies the IMMEDIATE option aborts or is aborted by the user, the repacking might be incomplete; that is, some data blocks are of their original size, while others are of the newly specified size.

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

If no DATABLOCKSIZE specification is specified in the ALTER TABLE request, then the data block size is not changed and no data blocks are repacked.