16.10 - Base Table Row Format - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)
The row format diagrams and the examples in this chapter portray a system with 64 KB rows. A system with larger, 1 MB rows is not shown.

The structure of Teradata Database base table rows is slightly different for rows in the following rectangular conditions.

  • A table having an nonpartitioned primary index versus a table having a partitioned primary index.
  • A system using byte-packed format referred to as packed64 format versus a system using 64-bit byte-aligned referred to as aligned row format.
  • A table with load isolation versus a table without load isolation. Load isolation allows committed reads during table loads on tables that are created or altered to be load isolated. For more information, see Database Administration.

Row format information is used to make fine-grained row size estimates for capacity planning.

General Row Structure

The following topics describe the structure of a Teradata Database row in systems using packed64 format and systems using aligned row format. Whether a system stores its data in packed64 or aligned row format depends on the settings on several factors your Teradata support personnel can modify. The size of tables on a system that stores data in packed64 format is generally between 3% and 9% smaller than the size of the same tables on a system that stores data in aligned row format (the average difference is roughly 7% smaller for the packe64d row format). Storing data in packed64 format reduces the number of I/O operations required to access and write rows in addition to saving disk space.

Either 12 or 16 bytes of the row header are devoted to overhead, depending on whether the table has a non-partitioned or a partitioned primary index and whether the row format of your system is 1 MB or 64 KB rows. Systems with 64 KB rows have 12 bytes devoted to overhead, and systems with 1 MB rows have 16 bytes devoted to overhead. The maximum row length is 1 MB. This limit is the same for both packed64 and aligned row formats. This maximum length does not include BLOB, CLOB, or XML columns, which are stored in special subtables outside the base table row. See Sizing a LOB or XML Subtable for information about BLOB, CLOB, and XML subtables.

The number of characters that can be represented by this number of bytes varies depending on whether characters are represented by one byte, two bytes, or a combination of single-byte and multibyte representations.

There are three general categories of table columns, which are stored in the row in the order listed:

  1. Fixed length.

    Storage is always allocated in a row for these columns.

  2. Compressible.

    Includes both value-compressed and algorithmically compressed data and can be stored in any order.

    FOR this type of column … Storage is allocated …
    Multivalue-compressible in a row when needed.

    No disk storage space is required when the column contains a compressed value as specified in the CREATE TABLE DDL text.

    If a column value is not compressed, then Teradata Database allocates storage space for it.

    algorithmically-compressible in a row when needed.

    If a column is not multivalue compressed or NULL, Teradata Database allocates storage space for it.

    Algorithmically-compressed data requires less space.

  3. Variable length.

    Storage space is allocated in the row depending on the size of the column.

The structure of Teradata Database base table rows varies slightly for tables having an nonpartitioned primary index versus a partitioned primary index (see Row Structure for Packed64 Systems and Row Structure for Aligned Row Format Systems for details).

For a system using the packed64 format, columns are stored in field ID order, with the fixed length fields first followed by the compressed fields, and last by the variable length fields.

In aligned row format, fixed length columns are stored first, followed by compressed fields, and then by variable length fields. This ordering is the same as the ordering for packed64 format storage with the exception that the columns within each category are stored in decreasing alignment constraint order.

For example, if a row contains fixed length columns with the types CHARACTER, INTEGER and FLOAT, the floating point numbers are stored first, followed by the INTEGER numbers, and then by the CHARACTER columns regardless of the order in which the columns were defined in the CREATE TABLE request defining the table.

Teradata Database uses a space optimization routine for aligned format rows that can store a maximum of 7 bytes of data in the potentially unused space that can occur when a column is aligned on a 0(mod 8) boundary. After the routine fills the available space with candidate date, Teradata Database follows the rules outlined in the preceding paragraphs to complete the remainder of the row data in an aligned format row.

Sector Alignment

The Teradata File System supports devices that use either a native 512 byte sector size or a native 4KB sector size.

The new 4KB disk drives can be separated into 2 classes: those that support I/O on non-4KB aligned disk boundaries and those that do not support non-4KB aligned I/O. When operating on system with 4KB drives, Teradata Database only performs I/Os on blocks that are full 4KB aligned (in size and length), regardless of the class of the drive.

Teradata Database only supports a homogeneous alignment configuration throughout the entire system. On a given system, data on all storage devices is aligned or unaligned, and cannot be both. This also applies across cliques.

General Row Structure When Compressing Variable Length Columns

The following information describes aspects of general row structure when one or more variable length columns in a table are compressed. The description applies to columns having any of the following data types:

  • VARBYTE
  • VARCHAR
  • VARCHAR(n) CHARACTER SET GRAPHIC
  • UDT
  • CLOB/BLOB

The description assumes that all variable length compressible columns are treated as an extension of fixed length compressible columns except that decompressed variable length columns store both a length and the actual column data. There is an additional presence bit for an algorithmically compressed column to indicate whether the column data is compressed or not. Teradata Database sets this bit only when data in a column is compressed using algorithmic compression.

When column data is compressed algorithmically, Teradata Database stores it as length: data pairs interleaved with the other compressible columns in the table. When column data is null, Teradata Database does not store a length, so there is no overhead in that case.

The following summary information applies to general row structure elements for the storage of variable length column data for multivalue compression.

  • When compression does not apply to a value in a column that specifies multivalue compression, Teradata Database stores the column data in the row as a length and data value pair.

    If the length of the column data is 255 bytes, then Teradata Database stores the length in 1 byte; otherwise it stores the length in 2 bytes.

    A variable length column (without compression) always has a 2-byte offset associated with it, whereas a compressed variable length column can have its length stored in one or two bytes, depending on whether the column length is 255 or not, respectively.

  • Teradata Database stores uncompressed variable length data in the row as a length and data value pair.

    The stored length for multivalue compressed data is the length of the uncompressed column value.

  • If a variable or fixed length multivalue compressed column is compressed or is null, then its values are not stored in the row.

    Instead, Teradata Database stores one instance of each compressed value within a column in the table header and references it using the presence bits array for the row.

  • If a fixed length multivalue compressed column is not compressed, then Teradata Database stores its values in the row as data, but without an accompanying length. No length is needed because Teradata Database pads fixed length data values to the maximum length defined for their containing column.

The following summary information applies to general row structure elements for the storage of variable length column data for algorithmic compression.

  • If a variable length column is not compressed, then Teradata Database stores its values in the row as a length and data value pair.
  • If a fixed length column is not compressed, then Teradata Database stores its values in the row as data, but without an accompanying length.

    Teradata Database pads fixed length data values to the maximum length defined for their containing column.

  • For an algorithmically-compressed column with a variable length, Teradata Database stores its values in the row as a length and data value pair.

    The stored length for algorithmically-compressed data is the length of the compressed column value, not its original, uncompressed length.

    If the length of the column data is 255 bytes, then Teradata Database stores the length in 1 byte; otherwise, it stores the length in 2 bytes.

  • For an algorithmically-compressed column with either a variable length or a fixed length, Teradata Database does not store a representation of its data in the row if the column is null, but does indicate its existence in the data using the presence bit array for the row.

The following sets of examples demonstrate the specific effects on row structure for multivalue compression alone (cases 1, 2, and 3 of example 1), algorithmic compression alone (cases 1, 2, and 3 of example 2), and combined multivalue and algorithmic compression (cases 1, 2, and 3 of example 3). In each case, the row structure diagram is based on the structure for a partitioned table (seePacked64 Row Structure for a Partitioned Table ) even though the examples are all for nonpartitioned primary index tables.

Example 1: Algorithmic Compression But No Multivalue Compression

Suppose you define the following table to use Huffman encoding algorithms to compress and decompress two of its columns algorithmically. This table specifies algorithmic compression on columns alc1 and alc2, but no multivalue compression. Particularly relevant data for each case is highlighted in boldface type.

     CREATE TABLE t1 (
       fc1  INTEGER,
       alc1 VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp
                                 ALGDECOMPRESS huffdecomp,
       vc1  VARCHAR(20),
       alc2 VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp
                                 ALGDECOMPRESS huffdecomp,
       vc2  VARCHAR(20) );

Field 5 of the table header for this table has the following field descriptors.

                         UDT or
              Num-  Next Com-                   Pres-
In- Fld  Off- Comp- Fld  press  Calc   Sto-     ence     Sort
dex id   set  ress  Ind  offset offset rage     Byte Bit Desc   EvlRepr
--- ---- ---- ----- ---  ------ ------ -------- ---- --- ------ ------------------
 1  1025  20         2     0    Offset Nullable   0   1  AscKey DBC INTEGER
 2  1026  -          4    196   Comprs alc+Null1  0   2  NonKey DBC VARCHAR(10) LATIN
 3  1027  12         5     0    Var    Nullable   0   4  NonKey DBC VARCHAR(20) LATIN
 4  1028  -          3    206   Comprs alc+Null1  0   5  NonKey DBC VARCHAR(10) LATIN
 5  1029  14         0     0    Var    Nullable   0   7  NonKey DBC VARCHAR(20) LATIN

To be as general as possible, each row structure diagram indicates the configuration of fields as they would be if the table had a partitioned primary index, though none of the actual table creation SQL text specifies a PPI. The diagrams also assume the system has a Packed64 row structure.

Case 1

Suppose you have the same table, but without any algorithmic compression being defined. The row structure for this table looks something like the following diagram.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed length data type.
len1 Length of the data in column alc1, an algorithmically-compressed variable length data type.
alc1 Algorithmically-compressed data for column alc1, a variable length data type.
len2 Length of the data in column alc2, an algorithmically- compressed variable length data type.
alc2 Algorithmically-compressed data for column alc2, a variable length data type.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, a variable-length data type.

In this case, the data values for columns alc1 and alc2 are both stored in the row and are not compressed.

Teradata Database has placed the variable length compressible columns after the fixed length columns in the compressible columns area of the row. This is also reflected in the Field5 descriptor. Because the columns are not compressed, Teradata Database stores lengths with the two alc columns. len1 and len2 contain the uncompressed lengths of those columns, and the ALC bit is not set.

Case 2

In the next case, algorithmic compression has been defined, and the data for columns alc1 and alc2 is not null. alc1 and alc2 are present in the row and compressed. len1 and len2 contain the new compressed length. The ALC bit has been set for this case to indicate that the data in columns alc1 and alc2 is compressed.

For this case, the row structure for the table superficially looks exactly like the row structure diagram for the previous case.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed length data type.
len1 Length of the data in column alc1, an algorithmically-compressed variable length data type.
alc1 Algorithmically-compressed data for column alc1, a variable length data type.
len2 Length of the data in column alc2, an algorithmically-compressed variable length data type.
alc2 Algorithmically-compressed data for column alc2, a variable length data type.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, a variable-length data type.

Case 3

In the next case, algorithmic compression has been defined, but the data for columns alc1 and alc2 is null. alc1 and alc2 are present in the row and compressed. len1 and len2 contain the new compressed length. The ALC bit is not set for this case because column alc1 is null.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed-length data type.
len1 Not represented.

This column is null, so no length for column alc2 is stored in the row.

alc1 Not represented.

This column is null, so no value for alc1 is stored in the row.

len2 Length of the data in column alc2, an algorithmically-compressed variable length data type.
alc2 Algorithmically-compressed data for column alc2, a variable length data type.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, a variable-length data type.

Example 2: Multivalue Compression But No Algorithmic Compression

This example presents row structure cases based on the following table definition. This table has multivalue compression defined on columns mvc1 and mvc2, but no algorithmic compression. Particularly relevant data for each case is highlighted in boldface type.

     CREATE TABLE t1 (
       fc1  INTEGER,
       mvc1 VARCHAR(10) COMPRESS (‘mars’,’saturn’,’jupiter’),
       vc1  VARCHAR(20),
       mvc2 VARCHAR(10) COMPRESS (‘Germany’,’France’,’England’),
       vc2  VARCHAR(20) );

Field 5 of the table header for this table has the following field descriptors.

                         UDT or
              Num-  Next Com-                   Pres-
In- Fld  Off- Comp- Fld  press  Calc   Sto-     ence     Sort
dex id   set  ress  Ind  offset offset rage     Byte Bit Desc   EvlRepr
--- ---- ---- ----- ---  ------ ------ -------- ---- --- ------ ------------------
 1  1025  20         2     0    Offset Nullable   0   1  AscKey DBC INTEGER
 2  1026  -          4    196   Comprs cmp+Null2  0   2  NonKey DBC VARCHAR(10) LATIN
 3  1027  14         5     0    Var    Nullable   0   5  NonKey DBC VARCHAR(20) LATIN
 4  1028  -          3    206   Comprs cmp+Null1  0   6  NonKey DBC VARCHAR(10) LATIN
 5  1029  16         0     0    Var    Nullable   0   1  NonKey DBC VARCHAR(20) LATIN

Case 1

In this case, the data values for columns mvc1 and mvc2 are both stored in the row because they are not compressed for the particular data values they contain.

Teradata Database has placed the variable length compressible columns after the fixed length columns in the compressible columns area of the row. This is also reflected in the Field5 descriptor. len1 and len2 contain the uncompressed lengths of those columns.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed-length data type.
len1 Compressed length of the data in column mvc1, a multivalue compressed variable length column.
mvc1 Multivalue compressed data for column mvc1.
len2 Compressed length of the data in column mvc2, a multivalue-compressed variable length column.
mvc2 Multivalue compressed data for column mvc2.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, a variable-length data type.

Case 2

The next case demonstrates row storage when the column data in the row is to be multivalue compressed. In this case, the value for column mvc1 is not stored in the row because it is compressed. The value is instead stored in the table header and referenced by the presence bits array for the row.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation                                                                Definition
fc1 Uncompressed data for column fc1, a fixed-length data type.
len1 Not represented.

This column is null, so no length for column mvc1 is stored in the row.

mvc1 Not represented.

This column is null, so no value for mvc1 is stored in the row.

len2 Compressed length of the data in column mvc2, a multivalue-compressed variable-length column.
mvc2 Multivalue compressed data for column mvc2.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, an uncompressed variable-length data type.

Case 3

The next case demonstrates row storage when the column data for mvc1 is null. In this case, the value for column mvc1 is not stored in the row because nulls are always compressed. This demonstrates that nulls never incur a cost overhead for compression.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed-length data type.
len1 Not represented.

This column is null, so no value for mvc1 is stored in the row.

mvc1 Not represented.

This column is null, so no value for mvc1 is stored in the row.

len2 Compressed length of the data in column mvc2, a multivalue compressed variable-length column.
mvc2 Multivalue compressed data for column mvc2.
vc1 Uncompressed data for column vc1, a variable-length data type.
vc2 Uncompressed data for column vc2, a variable-length data type.

Example 3: Mix of Multivalue and Algorithmic Compression

This example presents row structure cases based on the following table definition. This table has multivalue compression defined on columns mvc_f1 and mvc_v1, and algorithmic compression defined on column alc1. Particularly relevant data for each case is highlighted in boldface type.

     CREATE TABLE t1(
       fc1    INTEGER,
       alc1   VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp
                                   ALGDECOMPRESS huffdecomp,
       vc1    VARCHAR(20),
       mvc_f1 CHARACTER(10) COMPRESS (‘Germany’,’France’,’England’),
       mvc_v1 VARCHAR(20) COMPRESS (‘Nike’,’Reebok’,’Adidas’) );

Field 5 of the table header for this table has the following field descriptors.

                         UDT or
              Num-  Next Com-                   Pres-
In- Fld  Off- Comp- Fld  press  Calc   Sto-     ence     Sort
dex id   set  ress  Ind  offset offset rage     Byte Bit Desc   EvlRepr
--- ---- ---- ----- ---  ------ ------ -------- ---- --- ------ ------------------
 1  1025  20         2     0    Offset Nullable   0   1  AscKey DBC INTEGER
 2  1026  -          4    196   Comprs alc+Null1  0   2  NonKey DBC VARCHAR(10) LATIN
 3  1027  14         0     0    Var    Nullable   0   5  NonKey DBC VARCHAR(20) LATIN
 4  1028  -          5    226   Comprs cmp+Null2  0   6  NonKey DBC VARCHAR(10) LATIN
 5  1029  -          3    256   Comprs cmp+Null2  1   1  NonKey DBC VARCHAR(20) LATIN

Case 1

In this case, no compression has been specified for the particular data values, so data for columns alc1, mvc_f1, and mvc_v1 is stored in the row.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed length data type.
len1 Compressed length of the data in column alc1, an algorithmically-compressed variable length data type.
alc1 Algorithmically-compressed data for the variable length column alc1.
mvc_f1 Multivalue compressed data for the fixed length column mvc_f1.
len2 Length of the data in column mvc_f1, a multivalue compressed variable length data type.
mvc_v1 Multivalue compressed data for the variable length column mvc_v1.
vc1 Uncompressed data for column vc1, a variable length data type.

Teradata Database has placed the variable compressible columns after the fixed field columns in the compressible columns area. This is also reflected in the Field5 descriptor. The compressed column data is interleaved with other compressible fields. len1 and len2 contain the uncompressed lengths of alc1 and mvc_v1, respectively.

Case 2

In this case, Teradata Database compresses the data for columns mvc_v1 and alc1 because the values for the row are specified in the multivalue for those columns.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation                                                              Definition
fc1 Uncompressed data for column fc1, a fixed length data type.
len1 Compressed length of the data in column alc1, an algorithmically-compressed variable length data type.
alc1 Algorithmically-compressed data for column alc1, a variable length data type.
mvc_f1 Multivalue compressed data for column mvc_f1, a fixed length data type.
mvc_v1 Not represented.

This column is compressed, so no value for mvc1 is stored in the row.

vc1 Uncompressed data for column vc1, a variable length data type.

In this case, Teradata Database does not store the value for mvc_v1 in the row because it is compressed for the particular data value, so the value is stored in the table header. len1 contains the compressed length for alc1.

Case 3

In this case, the data for the compressible columns mvc_v1 and alc1 is null, so Teradata Database does not store values for those columns in the row. This example demonstrates that nulls never incur a cost overhead for compression.



The following abbreviations are used for the various fields of the diagram for this case.

Abbreviation Definition
fc1 Uncompressed data for column fc1, a fixed length data type.
len1 Not represented.

This column is null, so no value for alc1 is stored in the row.

alc1 Not represented.

This column is null, so no value for alc1 is stored in the row.

mvc_f1 Multivalue compressed data for column mvc_f1, a fixed length data type.
len2 Not represented.

This column is null, so no value for mvc_v1 is stored in the row.

mvc_v1 Not represented.

This column is null, so no value for mvc_v1 is stored in the row.

vc1 Uncompressed data for column vc1, a variable length data type.

Row Structure for Packed64 Systems

Base table rows are stored in packed format on packed64 format systems, so they need not align on 8-byte boundaries. Because of this, their row structure is simpler than that of equivalent base table rows on aligned row format systems (see Row Structure for Aligned Row Format Systems) below.

Note that the Row Hash value is 4 bytes wide irrespective of the number of hash buckets the system has.

Packed64 Row Structure for a Nonpartitioned Primary Index Table with Load Isolation

The following graphic illustrates the basic structure of a Teradata Database row from a table on a Packed64 format system with an nonpartitioned, or traditional, primary index. The table is load isolated, so the RowLoadID field is present.



Packed64 Row Structure for a Partitioned Table

The following graphic illustrates the basic structure of a Teradata Database row from a table on a packed64 format system with a partitioned primary index:



The difference between this and the format of a nonpartitioned primary index row is the presence of a an additional 2-byte or 8-byte partition number field, which is also a component of the RowID (partitioned table rows are an additional 4 bytes wider if they also specify multivalue compression). It is this field that generates the need for a BYTE(10) data type specification for a RowID. For nonpartitioned primary index tables, the partition number is assumed to be 0, so the rowID of an nonpartitioned primary index table is also logically BYTE(10) (see ROWID Columns).

Row Structure for Aligned Row Format Systems

The row structure diagrams for aligned row format systems differs from those of packed64 systems by having five additional pad byte fields to ensure row alignment on an 8-byte boundary. The following table lists each of these pad fields and explains their purpose:

Pad Byte Field Name Purpose
VARCHAR Offsets Array Alignment Pad Bytes Aligns VARCHAR offsets array at a 2-byte boundary.
Fixed Length Column Alignment Pad Bytes Aligns fixed length columns.
Compressible Length Column Alignment Pad Bytes Aligns value compressible length columns.
VARCHAR Column Alignment Pad Bytes Aligns variable length columns.
Trailing Pad Bytes Aligns entire row on an 8-byte boundary.

Note that the Row Hash value is 4 bytes wide irrespective of the number of hash buckets the system has.

Aligned Row Structure for a Nonpartitioned Table with Load Isolation

The following graphic illustrates the basic structure of a Teradata Database row from a table on an aligned row format system with a nonpartitioned, or traditional, primary index. The table is load isolated, so it has the RowLoadID field.



Aligned Row Structure for a Partitioned Table With 65,535 or Fewer Combined Partitions

The following graphic illustrates the basic structure of a Teradata Database row from a table on an aligned row format system with a partitioned primary index that has 65,535 or fewer combined partitions:



The difference between this and the format of an nonpartitioned table row is the presence of a 2-byte or 8-byte partition number field, which is also a component of the RowID (partitioned table rows are an additional 4 bytes wider if they also specify multivalue compression. It is this field that generates the need for a BYTE(10) data type specification for a RowID. For nonpartitioned primary index tables, the partition number is assumed to be 0, so the rowID of an nonpartitioned primary index table is also logically BYTE(10) (see ROWID Columns).

Containers and Subrows

The data in a column-partitioned table can be stored in containers or subrows. The following table defines these terms.

Containers and Space

If Teradata Database can pack many column partition values into a container, this form of compression, called row header compression, can reduce the space needed for a column-partitioned table or join index compared to the same object without column partitioning.

If Teradata Database can place only a few column partition values in a container because of their width, there can actually be a small increase in the space needed for a column-partitioned table or join index compared to the same object without column partitioning. In this case, ROW