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

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

This topic describes a procedure for calculating the physical size of a row for a typical non-column-partitioned table stored on a system with a packed64 format architecture. The procedure does not account for BLOB, CLOB, or XML data, which are stored in 64 KB pieces in a subtable outside the base table row (see Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables and Sizing a LOB or XML Subtable), nor does it account for spool rows, which can be approximately 1MB long.

Use the Row Size Calculation form (see Sample Worksheet Forms to record your calculations.

The following employee table row definition is used as a sample for this procedure:

Employee
Employee
EmpNum SupEmpNum DeptNum JobCode LName FName HireDate BDate SalAmt
PK, SA FK FK FK NN NN NN NN NN
INTEGER INTEGER INTEGER SMALLINT CHAR(20) VARCHAR(30) DATE DATE DECIMAL(10,2)

Procedure for Packed64 Systems

Note the following points about sizing UDT columns.
  • The size of a distinct UDT column is identical to the size of the underlying predefined data type for the UDT.
  • The size of a structured UDT column is more difficult to determine and is not equivalent to the sums of the sizes of its individual underlying predefined data types. The calculation is further complicated by the fact that structured types can be nested to a maximum of 512 levels.

    See Sizing Structured UDT Columns for details about the composition and sizing of structured UDTs.

    determine the sizes of each individual structured UDT

This table used for this example has no LOB or UDT columns, so you would skip steps 14, 15, and 16 of the procedure, jumping from step 13 directly to step 17.

Follow this procedure to determine the physical size of a typical row for any given non-LOB table:

  1. List all the varying length columns in the four columns labeled Variable Data Detail.
  2. For each variable length column, estimate the average number of bytes expected.
  3. Add the total number of bytes in varying length columns and record the figure in the column labeled SUM(a).

    In the example table, there is only one varying length column, FName, which is typed VARCHAR(30). Its average number of bytes is estimated to be 14, so the value for SUM(a) is 14.

  4. Determine how many columns in the table there are for each fixed byte data type.

    The example table has the following number of each fixed byte data type:

                             Data Type              Number of Columns
    DATE                             2
    DECIMAL                             1
    INTEGER                             3
    SMALLINT                             1
  5. Enter these figures in the Number of Columns column next to each relevant data type.

    Remember that all row lengths must be an even number of bytes (see Byte Alignment), so be sure to take this into account.

  6. Multiply the counts by the sizing factor provided for the type and enter the results under the Total column.
  7. Enter the total byte counts for the fixed length character types in the SUM(n) column.
    • CHARACTER
    • BYTE
    • GRAPHIC

      For the example, only one CHARACTER column with a byte count of 20 is found, so enter 20 as the result for SUM(n).

  8. Add the values for SUM(n) and SUM(a) and record them in the column labeled Logical Size.

    For the example, the logical size is 64 bytes.

  9. The overhead for any nonpartitioned primary index row is 14 bytes. For a PPI row, the overhead is 18 bytes. Those numbers are prerecorded for you in the column labeled Overhead. Use the appropriate column for the primary index type of the table.
  10. Multiply the number of variable length columns by 2 to account for the 2-byte variable column offset pointers determined in step 1 of this procedure.

    Write the value in the column labeled Variable Column Offsets.

    For the example, there is only one variable column, so write the number 2 here (1 x 2 = 2).

  11. Record the number of columns compressed on a non-null value.
  12. Record the number of nullable columns.
  13. Divide the sum of step 11 and step 12 by 8 and record the quotient of the operation.

    For aligned row format systems, you will use this value again at step 20a.

    The purpose of this step is to account for any required additional presence bits that might be required.

    For the example, the calculation is 3/8, so the quotient is 0.

  14. Determine how many BLOB, CLOB, or XML columns are in the table and record the number under Number of Columns on the Row Size Calculation Form, Page 1 of 2. Multiply that number by 40 to determine the total row size taken up by BLOB, CLOB, and XML object IDs (OIDs).
  15. Compute and record the sizes of any UDT columns on page 2 of 2 of the Row Size Calculation Form as follows:
    1. Column 1 records the name of the UDT being recorded.
    2. Column 2 records how many columns in the table have that type.
    3. Column 3 records the sizing factor for the UDT.

      This is the physical size of the column, which is one of the following.

      FOR this UDT type … The physical size is the …
      distinct size of its underlying predefined data type.
      structured calculated size of the column.
      ARRAY/VARRAY
      • size of its underlying predefined data type if it is a one-dimensional array.
      • calculated size of the column if it is a multidimensional array.
    4. Column 4 records the product of the number of columns and the sizing factor for the UDT.
  16. Sum the UDT totals and record them on Page 1 of 2 of the Row Size Calculation Form in the cell labeled UDTs.
  17. Add the integers recorded in the Total column and record the sum in the column labeled Physical Size.
  18. If the sum is an uneven number, round it up to the next even number.

    For the example, the sum is 82, so no rounding is necessary.

  19. Whether you continue or not depends on the addressing used by your system.
    IF you are calculating the row size for this type of system … THEN …
    packed64 stop.
    aligned row continue to step 20.
  20. Determine the byte alignment overhead for the row.
    1. Set the value of 64-bit_byte_alignment_bit_overhead to 0 and consult the number of additional presence bits determined in Step 13 of this procedure.
      IF the value recorded in Step 13 is an … THEN set 64-Bit_Byte_Alignment_Bit_Overhead to this value …
      odd number

      AND

      the number of variable length columns in the row is 1

      1
      even number 0
    2. Determine the maximum alignment among all the fixed length columns in the row.

      Call this variable FA.

    3. Determine the maximum alignment among all compressible columns in the row.

      Call this variable CA.

    4. If there are no fixed length or value compressible columns in the row, set the value of FA or CA to 1.
  21. Increment the value of Byte_Alignment_Bit_Overhead by MAX(FA,CA) - 1.
  22. Determine the maximum alignment of all variable length columns.

    Call this variable VA.

    If there are no variable length columns, set VA to 1.

  23. Increment the value of 64-Bit_Byte_Alignment_Bit_Overhead by (VA - 1)
  24. Add the value of 64-Bit_Byte_Alignment_Bit_Overhead to the aligned row size determined by Steps 14 - 17 in this procedure.
  25. Round up the value determined in Step 24 to the nearest multiple of 8.

    This value is the row size in bytes for an aligned row format system.

Note that steps 20 - 25 are a conservative approximation of the row size for an aligned row format system.

Procedure To Determine the Exact Row Size for Aligned Row Systems

The following procedure determines the exact row size for aligned row format systems.

  1. Set the initial value for RowSize to 12 bytes and the initial value for 64-Bit_Byte_Alignment_Bit_Overhead to 0 bytes.
  2. Record the number of columns compressed on a non-null value.
  3. Record the number of nullable columns.
  4. Divide the sum of step 2 and step 3 by 8 and record the quotient of the operation.

    Call this variable PB.

    The purpose of this step is to account for any required additional presence bits that might be required.

    For example, if the ratio is 3/8, then the quotient is 0.

  5. Overwrite the value for RowSize as follows:

    RowSize equation

  6. Record the number of variable length columns.

    Call this variable VC.

  7. Determine the space required for the variable length columns offset array using the following pseudocode procedure:
    IF VC > 0
      THEN
        IF (RowSize is odd)
          THEN
            RowSize += 1
            64BitOverhead += 1
        ENDIF
      RowSize = RowSize + 2 * (VC + 1)
    END IF

    where += is the assignment operator.

  8. Determine the maximum alignment required for all fixed length columns.

    Call this variable FA.

  9. Determine the size of all fixed length columns.

    Call this variable FS.

  10. Determine the maximum alignment required for all compressible columns.

    Call this variable CA.

  11. Determine the size of all compressible columns.

    Call this variable CS.

  12. Determine the maximum alignment required for all variable length columns.

    This includes BLOB, CLOB, and XML columns, VARCHAR columns, and VARBYTE columns. Because BLOB, CLOB and XML values are stored in subtables outside of the row, this calculation should more correctly be referred to as a table size determination rather than a row size determination.

    Call this variable VA.

  13. Determine the size of all variable length columns.

    Call this variable VS.

  14. Calculate the actual size value (call it FixedActual) for fixed length columns using the following equation, where FP represents row size:

    FixedActual = (FP+FA-1) - (FP+FA-1) MOD(FA)

    The modulo(FA) adjustment aligns the value of FixedActual to the nearest multiple of FA.

  15. Calculate the fixed length column overhead (call it B) using the following equation:

    B = FixedActual - FP

  16. Set RowSize = FixedActual + FS.

    Call this variable CP.

  17. Calculate the actual size value (call it CompressActual) for compressible length columns using the following equation:

    CompressActual = (CP+CA-1)-(CP+CA-1)MOD(CA)

    The modulo(CA) adjustment aligns the value of CompressActual to the nearest multiple of CA.

  18. Calculate the value compressible column overhead (call it C) using the following equation:

    C = CompressActual-CP

  19. Set RowSize = CompressActual + CS.
  20. Set 64-Bit_Byte_Alignment_Bit_Overhead = 64-Bit_Byte_Alignment_Bit_Overhead + B _ C.
  21. Adjust the values of RowSize and 64-Bit_Byte_Alignment_Bit_Overhead if B + C > MAX(FA,CA) using the following pseudocode procedure:
    IF((B+C) > MAX(FA,CA))
     THEN
      RowSize = RowSize - CA
      Byte_Alignment_Bit_Overhead = Byte_Alignment_Bit_Overhead - CA
    END IF
  22. Assign RowSize to VP.
  23. Calculate the actual maximum alignment required for variable length columns using the following equation:

    VA_Actual = (VP+VA-1)-(VP+VA-1)MOD(VA)

    The modulo(VA) adjustment aligns the value of VA_Actual to the nearest multiple of VA.

  24. Set Byte_Alignment_Bit_Overhead = Byte_Alignment_Bit_Overhead + VA_Actual - VP.
  25. Set RowSize = VA_Actual + VS.
  26. Calculate the actual value for RowSize using the following equation:

    RowSize = (RowSize+7) - (RowSize + 7)MOD(8)

    The modulo(8) adjustment rounds the value for RowSize upward to the nearest multiple of 8.

  27. Calculate the actual value for 64-Bit_Byte_Alignment_Bit_Overhead using the following equation:

    64-Bit_Byte_Alignment_Bit_Overhead = 64_Bit_Overhead + (RowSize + 7) - (RowSize + 7)MOD(8)

    The modulo(8) adjustment rounds the value for 64-Bit_Byte_Alignment_Bit_Overhead upward to the nearest multiple of 8.

  28. Calculate the total aligned row format size by adding the values of RowSize and 64-Bit_Byte_Alignment_Bit_Overhead using the following equation:

    64-Bit_Byte_Alignment_Bit_Overhead = RowSize + 64 - 64-Bit_Byte_Alignment_Bit_Overhead