15.00 - Things To Consider When Filling Out the Row Size Calculation Form - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Things To Consider When Filling Out the Row Size Calculation Form

Keep the following things in mind as you fill out the Row Size Calculation form:

  • The size of the row ID and row header depends on the table partitioning.
  •  

    Table

    Row ID Size (bytes)

    Row Header Size (bytes)

    not partitioned

    8

    12

    up to 15 partition levels (65,535 combined partitions)

    2-bytes used for partition level number in row ID portion of row header

    10

    14

    up to 62 partition levels (more than 65,535 combined partitions)

    8-bytes used for partition level number in row ID portion of row header

    16

    20

    These values are true whether the table has a primary index or not.

  • BLOB, CLOB, and XML column values are stored outside the row.
  • The value you should enter for BLOB, CLOB, and XML columns in the Row Size Calculation form is their Object Identifier, which is always 39 bytes.

    The size of each XML, BLOB, or CLOB subtable must be calculated separately (see Chapter 14: “Database-Level Capacity Planning Considerations” for details).

  • The following system-derived columns consume the following amount of space per row, respectively. Note that the first ROWID can only occur in a join index, and the second two only in result rows.
  •  

    System‑Derived Column Name

    Data Type

    Number of Bytes Stored for the Internal Information From Which They Are Derived

    ROWID

    BYTE

  • 10 for 2‑byte partitioning or no partitioning
  • 16 for 8‑byte partitioning
  • PARTITION

     

    INTEGER

    4 for 2-byte partitioning

    BIGINT

    8 for 8-byte partitioning

    PARTITION#Ln

     

    INTEGER

    4 for 2-byte partitioning

    BIGINT

    8 for 8-byte partitioning

  • The following system‑generated columns consume the following amount of space per row, respectively.
  •  

    System‑Generated Column Type

                   Data Type

         Number of Bytes Stored

    Object Identifier (OID)

    VARBYTE

    39

    Identity

    Any of the following

  • BIGINT
  • BYTEINT
  • DECIMAL(n,0)
  • INTEGER
  • NUMERIC(n,0)
  • NUMBER(n,0)
  • You can only use the fixed form of NUMBER for identity columns. The floating form is not valid.

  • SMALLINT
  • 1 - 8, depending on the data type.

    This is true even when the DBS Control parameter MaxDecimal is set to 38, because the values generated by an identity column are restricted to a maximum of DECIMAL(18,0) NUMERIC(18,0) or NUMBER(18,0).

  • Compressed values and nulls have no effect on the length of their field in a column (they are “stored” as 0 bytes for the field), but can add to the row overhead because of their effect on the number of presence bits in the row header (for details, see “Presence Bits” on page 780).
  • Be aware that each compressed value consumes space in the table header (for details, see “Determining How Much Table Header Space is Used for Compression” on page 711).

  • The ending timestamp values for any PERIOD(TIMESTAMP) or PERIOD(TIMESTAMP WITH TIME ZONE) values stored with an UNTIL_CHANGED ending element value consume only 1 byte of storage for their ending element, while PERIOD(TIMESTAMP) and PERIOD(TIMESTAMP WITH TIME ZONE) values stored with a specified ending element value consume 20 and 24 bytes of storage, respectively, for their ending element.