15.00 - Procedure for Packed64 Systems - Teradata Database

Teradata Database Design

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

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” on page 792 for details about the composition and sizing of structured UDTs.

    Use “Equation: Structured UDT Size for Packed64 Format System” on page 795 to 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” on page 770), 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:

    a Column 1 records the name of the UDT being recorded.

    b Column 2 records how many columns in the table have that type.

    c 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 as determined by “Equation: Structured UDT Size for Packed64 Format System” on page 795.

    ARRAY/VARRAY

  • size of its underlying predefined data type if it is a one‑dimensional array.
  • calculated size of the column as determined by “Equation: Structured UDT Size for Packed64 Format System” on page 795 if it is a multidimensional array.
  • d 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 labelled 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.

    a 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

    b Determine the maximum alignment among all the fixed length columns in the row.

    Call this variable FA.

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

    Call this variable CA.

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

    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 .

    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.