IMMEDIATE DATABLOCKSIZE - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
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.