Procedure for Packed64 Systems - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
Structured/Distinct/Variant UDTs are only supported on the Block File System on the primary cluster. They are not available for the Object File System.
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 jump from step 13 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 the number of columns in the table 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.

    Each row length 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 their sum 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 use this value again at step 20a.

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

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

  14. Determine the number of BLOB, CLOB, or XML columns 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 the number of columns in the table with 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.

      UDT Type Physical Size
      Distinct Size of underlying predefined data type.
      Structured Calculated size of column.
      ARRAY/VARRAY
      • One-dimensional array: Size of underlying predefined data type.
      • Multidimensional array: Calculated size of column.
    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 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.
    System Type Action
    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 see the number of additional presence bits determined in Step 13 of this procedure.
      Value Recorded in Step 13 64-bit_byte_alignment_bit_overhead
      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.

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