Row Size Calculation Form | Database Design | Teradata Vantage - 17.10 - Row Size Calculation Form - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

The Row Size Calculation form is used to estimate the size of your tables in bytes. For details about computing row sizes, see Database-Level Capacity Planning Considerations.

This estimate is of the raw row size without considering all the aspects of physical database design that can affect table size both positively and negatively.

Sources for the Required Information

The following table points to the sources for the miscellaneous column-level data requested by the Row Size Calculation form.

Information Source
Column name Column name from your logical data model.
Type Table form reference to the Constraint form.
Max See Data Type Considerations.
Avg For variable length types, must be computed; otherwise, use the value for Max.

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 (for more information, see Database-Level Capacity Planning Considerations).

  • 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 more information, see Presence Bits).

    Be aware that each compressed value consumes space in the table header.

  • 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.

Procedure

Perform the following procedure to complete the Row Size Calculation form for each table.

  1. Identify each column in the table and enter its name in the Column Name column.
  2. Identify the data type for each column and enter it in the Type column next to the column name.
    IF the type is … THEN …
    predefined use the sizing factors on the right-hand side of the form to determine the size of values stored for each data type.
    BLOB, CLOB, or XML enter 39 bytes for the value of its OID.

    BLOB, CLOB, and XML values are stored outside of the row in their own subtables, which also must be accounted for in your capacity planning.

    a distinct, structured, or ARRAY UDT use the back of the form to tally its size.

    When you have identified and tallied all UDT columns in the table, copy their sum into the UDTs item in the sizing factors column on the right-hand side of the form.

  3. Determine the maximum value of the specified data type for each column in the table and enter the value in the Max column.
  4. Compute the average size of the specified data type for each column in the table and enter the value in the Avg column.
  5. Enter the total physical row size, rounded up to an even number of bytes, as Physical Size.