General Row Structure
The graphics in the two 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. The maximum row length is approximately 64 KB (the actual limit is 64,256 bytes) except for spool rows, which can be up to approximately 1MB long, and 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” on page 767 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 … |
|
Multi‑value‑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” on page 685 and “Row Structure for Aligned Row Format Systems” on page 687 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.