16.10 - Storage and Other Overhead Considerations for Partitioning - 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)

Implications for Storage of Partitioning

The storage implications of partitioned tables and join indexes are minimal.

  • Each row in a partitioned table or join index has either 2 or 8 additional bytes in its row header to store its internal partition number.
  • An empty partition occupies no space on disk, and a populated partition occupies only 2, 4, or 8 bytes more per row than the same rows would if they belonged to a nonpartitioned table because a partition is just an ordering of the rows on an AMP.
  • There is no additional overhead accrued by inserting the first row into a partition.

    Another way to say this is that there is no action required to create a partition. Rows are just inserted in their partition/row hash/uniqueness order.

  • There is no additional overhead accrued by deleting the last row of a partition.

    In other words, there is no additional action required to drop a partition. The last row of a partition is just deleted in the same way as any other row.

Storage Considerations for Column-Partitioned Tables and Join Indexes

Column-partitioned tables and join indexes are always stored in packed64 format.

A table with column partitioning has similar space usage and guidelines as a table with only row partitioning except that you must take the impact of column compression or expansion into account.

If the flag byte that precedes the first presence byte for a physical row has the first high-order and third bits both not set, the physical row is in a nonpartitioned table or join index and does not contain an internal partition number because the internal partition number for any nonpartitioned database object it is assumed to be 0.

If the first bit is set and third bit is not set, the physical row is in a table or join index that has 2-byte partitioning and that physical row contains a 2-byte internal partition number.

If the first bit is not set and the third bit is set, the physical row is in a table or join index that has 8-byte partitioning and the physical row contains an 8-byte internal partition number.

The following table summarizes this information.

Flag Byte Settings for Partitioning and Size of Internal Partition Number
First Bit Third Bit Description
Not set Not set Physical row is from a nonpartitioned table or join index.
Set Not set Physical row is from a partitioned table or join index that has 2-byte partitioning.
Not set Set Physical row is from a partitioned table or join index that has 8-byte partitioning.
Set Set Not used and reserved for future use.

Teradata Database stores the 2-byte or 8-byte internal partition number after the first presence byte of a physical row. Even though this information is stored after the hash value:uniqueness for a column-partitioned table or after the hash bucket:uniqueness for a nonpartitioned NoPI table, the file system orders physical rows according to their internal partition number first, followed by the hash value:uniqueness.

The first presence byte in a container is not used as a presence byte; instead, it is used to indicate information about autocompression for the container.

A column-partitioned table or join index can be much larger than an otherwise equivalent, but not column-partitioned, table or join index if there are few physical rows in populated combined partitions or there are many column partitions with ROW format and the subrows are narrow.

A container can only contain values of rows that have the same internal partition number and hash bucket value. The increased size occurs because of the increased number of physical rows and the overhead of the row header for each physical row.

However, a column-partitioned table or join index is usually smaller than a table or join index that is not column-partitioned if COLUMN format is used for narrow column partitions and the table or join index is not over-partitioned because of the reduced number of physical rows and autocompression.

Column Partitions With COLUMN Format

A column partition with COLUMN format packs column partition values into a physical row, or container, up to a system-determined limit. The column partition values must be in the same combined partition to be packed into a container.

The row header occurs once for a container instead of there being a row header for each column partition value.

The format for the row header is either 14 or 20 bytes and consists of the following fields.

  • Length
  • rowID

    The rowID of the first column partition value is the rowID of the container.

  • Flag byte
  • First presence byte

The rowID of a column partition value can be determined by its position within the container. If many column partition values can be packed into a container, this row header compression can greatly reduce the space needed for a column-partitioned object compared to the same object without column partitioning.

If Teradata Database can only place a few column partition values in a container because of their width, there can actually be an increase in the space needed for a column-partitioned object compared to the object without column partitioning. In this case, ROW format might be more appropriate.

If Teradata Database can only place a few column partition values because the row partitioning is such that only a few column partition values occur for each combined partition, there can be a very large increase in the space needed for a column-partitioned object compared to the object without column partitioning. In the worst case, the space required for the column-partitioned object can be as much as 24 times larger.

In this case, consider altering the row partitioning to allow for more column partition values per combined partition or removing column partitioning.

If the container has autocompression, 2 bytes are used as an offset to compression bits, 1 or more bytes indicate the autocompression types and their arguments, if any, for the container, 1 or bytes, depending on the number of column partition values, of autocompression bits, 0 or more bytes are used for a local value-list dictionary depending the autocompression type, and 0 or more bytes are used for present column partition values.

If the container does not have autocompression either because you specified NO AUTO COMPRESS for the column partition or because no autocompression types are applicable for the column partition values of the container, Teradata Database uses 0 or more bytes for column partition values.

The byte length of a container is rounded up to a multiple of 8.

The formats for single-column and multicolumn partitions with COLUMN format differ slightly, as listed by the following bullets.

  • A single-column partition with COLUMN format consists only of single-column containers. Each container represents a series of column values.
  • A multicolumn partition with COLUMN format consists of multicolumn containers. Each container represents a series of column partition values where the column partition value is made up of a set of values, one for each column in the partition.

See Row Structure for Containers (COLUMN Format) for more information.

Column Partitions With ROW Format

A physical row of a column partition with ROW format is called a subrow. Subrows have the same format as traditional Teradata Database rows, but only include the columns defined for the column partition. Note that currently there is no autocompression for subrows.

If Teradata Database uses ROW format for the column partitions of a column-partitioned object is such that many narrow column partition values occur, there can be a very large increase in the space needed for a column-partitioned object compared to the object without column partitioning. In the worst case, the space required for the column-partitioned object can be as much as 24 times larger.

Because of this, you should not specify ROW format for narrow column partitions. In the worst case, each column partition value can have a row header (14 or 20 bytes plus 6 or more bytes needed for a container) for each column partition value as compared to the same object without column partitioning. An object that is not column-partitioned only has a row header (14 or 20 bytes) for each regular row.

ROW format is useful for wide column partitions where one or only a few values fit in a container, and there is neither much benefit nor a negative impact from row header compression. ROW format provides quicker and more direct access to a specific column partition value than with COLUMN format because when a container has COLUMN format, Teradata Database must locate the column partition value within the container.

Depending on the autocompression types used for a container, access can be as simple as indexing into the container or it might require a sequential access through bits indicating how a value is compressed or sequential access through the column partition values, or both, to position to the specific column partition value to be accessed.

See Row Structure for Subrows (ROW Format) for more information.