17.10 - PermDBSize - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Specifies the maximum size for multirow data blocks in permanent tables. Rows that are larger than PermDBSize are stored in single-row data blocks, which are not limited by PermDBSize.

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.

On cliques with 4 KB aligned devices, the setting is rounded up to the next 8-sector multiple.

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 the DBS Control Record has been written.

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.

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

PermDBSize and System Performance

Database performance can be affected by the relationship of data block size to the type of work typically performed by the database:

  • When database queries are tactical in nature, involving one or a few table rows, it is advantageous to have fewer rows stored per data block to speed data access. Online transaction processing (OLTP) is an example of this type of work.
  • Alternatively, when database queries are strategic in nature, involving complex queries that involve many table rows per table, it is advantageous to have many rows stored in each data block, to minimize costly data I/O operations. Decision support software (DSS) and complex report generation are examples of this type of work.

PermDBSize sets the default maximum size used by the system for multirow data blocks in permanent tables. Use a larger value if the database is used primarily for strategic work, and a smaller value if the database is used primarily for tactical work.

In a mixed-work environment, determine a value for PermDBSize based on the kind of work typically performed by the database. For tables involved in other types of work, PermDBSize can be overridden on a table-by-table basis using the DATABLOCKSIZE option of the CREATE TABLE and ALTER TABLE SQL statements.