Table Headers | Database Design | Teradata Vantage - Table Headers - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

Each database table has an associated subtable that contains the table header (table metadata). One copy of the table header is stored on each AMP that stores rows of the table. The map associated with the table at the time the table was created or altered specifies which AMPs store the table rows.

Vantage uses table headers internally to maintain various information about each table. Table headers are described here primarily because they are used to store value-compressed column values (see Compression Types Supported by Vantage), and it is difficult to explain how compression works without having at least a superficial understanding of this file structure.

The size limit for a table header is 1 MB, so it is not possible to create a table defined with all possible features (maximum numbers of columns, indexes, and compressed values) without exceeding the defined byte limit. The maxima stated in Teradata System Limits are for the individual table parameters only.

Table Header Components

The following topics describe the individual fields of a Vantage table header as it is stored on disk. The maximum length for each field is the value for a thin table header.

Row Header

See Base Table Row Format for a description of row headers.

Field 1

Fixed length. Includes:
  • Row 1 header.
  • Offset array for the variable length columns in the table header, Used to locate those columns.
  • Table header row format version.
  • Internal ID of the database to which this table belongs.
  • Internal ID of the database to which space for this table is charged.
  • Table creation timestamp.
  • Last table update timestamp.
  • Last table archive timestamp.
  • Primary index flag.
  • Table structure version. Updated each time the table description is modified
  • Table structure version for host utilities. Incremented each time a structural change is made that makes a database dump obsolete.
  • Number of backup tables associated with this table.
  • Internal ID of the permanent journal table.
  • Table kind. Describes whether the table is permanent, temporary, volatile, or a join index. For temporary tables, volatile tables, and join indexes, also describes preserve-on-commit and transient journaling characteristics.
  • Journal type:
    • After
    • Audit
    • Both
    • Before
    • None
  • Protection type:
    • Fallback
    • Log
    • None
  • User journal flag. Describes whether the table is a user-defined journal or not.
  • Hash flag. Describes whether table is hashed or not.
  • Dropped flag. Set TRUE from the time the AMP receives a Drop Table step until the table is dropped in the End Transaction step. Otherwise set FALSE.
  • DDL change flag.Prevents attempts to update the table after its DDL has changed. Set TRUE when table DDL is modified.

    Host utility table dump sets the flag FALSE.

  • Byte count of the number of USIs defined on the table.
  • Host character set at the time the table was created.
  • Number of parent tables referenced by this table.
  • Number of child tables referencing this table.
  • Merge block ratio for the table.
  • Merge block ratio validity. Indicates whether the specified merge block ratio for the table is valid or unspecified.
  • Data block size for the table in bytes.
  • Data block size validity. Indicates whether the specified data block size for the table is valid or unspecified.
  • Percent free space for the table.
  • Percent free space validity. Indicates whether the specified percent free space for the table is valid or unspecified.
  • Disk I/O integrity checksum. Used to verify the integrity of user tables, hash indexes, join indexes, and secondary indexes.
  • Message class of primary step.
  • Message kind of primary step.
  • Message class of secondary step.
  • Message kind of secondary step.
  • Host ID.
  • Session number.
  • Request number.
  • Transaction number.
  • Table ID of base temporary table. Used only for materialized temporary tables.
  • Internal ID for primary key index.
  • Restart flag. Tracks restart and non-restart cases of restore jobs during build phase.
  • Row format, which specifies whether current environment is packed64 format or aligned row format.
  • Dummy space.
  • List of index descriptors for the table in Index ID order, one per primary and secondary index defined on the table.
  • Row 1 length. Duplicate specification of the length of row 1.
  • Map number.
  • Map slot.
  • 1 byte to indicate whether map is contiguous or sparse.

Field 2

Variable length.

Maximum length: 47,784 bytes.

Contains the primary index descriptor and all secondary index descriptors for the table.

Field 3

Not used.

Field 4

Variable length.

Maximum length: 423 bytes.

Contains MultiLoad, FastLoad, and table rebuild information.

Field 4 is always present for permanent journal tables, but is context-dependent for non-journal tables.

Field 5

Variable length.

Maximum length: 56,350 bytes.

Contains the following table column descriptors:
  • Internal ID of the first column in the table.
  • Number of varying length columns in the table.
  • Number of presence bits in each row. The upper limit for the number of presence and compress bits per row is 89,991.
  • Flag to indicate whether the table header is thin or fat. Used to determine whether the compressed value offsets list contains actual addresses or must be left-shifted to extract actual addresses.
  • Compression flag. Specifies whether the table has compressible columns or not.
  • Offset in the row to the presence bit array. Presence octet locations are determined by dividing the presence bit position by 8.
  • Offset in the row to the first byte past the presence bit array.
  • Number of columns in the table.
  • Index into the field descriptor array to the first compressible column. If no columns are compressed, then the value points to the first varying length column in the row.
  • Offset from the beginning of the row to the first optional (varying or compressible) data.
  • Index of the field descriptor for the first physical column in the row.
  • Field 5 type:
    • Table descriptor with row hash and unique rowID.
    • Index descriptor with row hash and unique rowID in the index.
    • Table descriptor with a partitioned rowID.
    • Index descriptor with a partitioned rowID in the index.
  • Row format. Specifies whether current environment is packed64 format or aligned row format.
  • Duplicate rows flag:
    • Dictionary and non-ANSI/ISO tables.
    • ANSI/ISO tables without unique indexes.
    • ANSI/ISO tables with unique indexes.
  • Offset to system code to build rows and to calculate PPI internal partition numbers.
  • Field descriptors array.
  • Compressed values and UDT contexts. Compressed values and a UDT context are stored just beyond the Field 5 descriptors and partition-related system code. The stored values are sorted first in ascending order of their binary values, then in descending order of their field size, and aligned on 2-byte boundaries. The stored UDT context is its autogenerated UDF constructor context. The area contains a 76-byte UDT context for every column typed with a UDT. This places a practical upper limit of approximately 1,600 UDT columns per table.
  • System code for building rows, including code to calculate partition numbers for the rows of row-partitioned tables. The row-partition-related system code is stored after the Field 5 descriptors, and just before the compressed values, if any have been defined.
  • UDT name stored as a variable length string of up to 128 Unicode characters.

Field 6

Variable length. Usually null.

Maximum length: 118 bytes.

Contains restartable sort and ALTER TABLE information.

Field 7

Variable length.

Maximum length: 94,592 bytes.

Contains:
  • Up to 128 reference index descriptors: 64 from a parent table to child tables and 64 from child tables to a parent table.See Sizing a Reference Index Subtable for a description of reference indexes.
  • List of the names of unresolved child tables from referential integrity constraint specifications as a variable length string of up to 128 Unicode characters.

Field 8

Variable length.

Maximum length: 520 bytes.

Contains BLOB, CLOB, and XML descriptors.

Field 9

Variable length.

Maximum length: 518 bytes.

Contains:
  • Length of, and offset to, the database name.
  • Length of, and offset to, the table name.
  • Database name (up to 128 Unicode characters).
  • Table name (up to 128 Unicode characters).

The database and table names are in the format databasename.tablename.

Field 10

Variable length.

No maximum length.

Description:
  • Indicates if any row-id ranges, or regions, of any data subtables have been marked down or invalid.
  • Each data subtable could have up to 6 invalid regions before the entire data subtable is marked invalid.

Field 11

Fixed length.

Maximum length: 82 bytes.

Description:
  • Indicates if the protected table is a FastLoad, MultiLoad, or partitioned restore error table.
  • Displays an array of field IDs of security constraint columns.
  • Displays an array of constraint IDs associated with the constraint columns.
  • Contains timestamps when the constraints were last altered.

Field 12

Additional information about the map the table uses, including:
  • Number of AMPs in the map.
  • Starting AMP number.
  • For sparse maps, colocation name.