The following topics describe the structure of a database row in systems using packed64 format and systems using aligned row format. Whether a system stores data in packed64 or aligned row format depends on the settings or factors your Teradata Support personnel can modify. The size of tables on a system that stores data in packed64 format is typically 3-9% smaller than the same tables on a system that stores data in aligned row format (the average difference is about 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 and saves disk space.
12 or 16 bytes of the row header are devoted to overhead, depending on whether the table has a nonpartitioned or 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 packed64 and aligned row formats. This maximum length does not include BLOB, CLOB, or XML columns, which are stored in subtables outside the base table row. See Sizing a LOB or XML Subtable.
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:
- Fixed length.
Storage is allocated in a row for these columns.
- Compressible.
Includes both value-compressed and algorithmically compressed data and can be stored in any order.
Column Type Storage Allocation 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 the database allocates storage space for it.
Algorithmically-compressible In a row when needed. If a column is not multivalue compressed or NULL, the database allocates storage space for it.
Algorithmically-compressed data requires less space.
- Variable length.
Storage space is allocated in the row depending on the size of the column.
Whether the primary index of a table is partitioned affects the structure of its base rows.
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.
Vantage 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, Vantage follows the rules outlined in the preceding paragraphs to complete the remainder of the row data in an aligned format row.