Procedure for Packed64 Systems
Note the following points about sizing UDT columns.
See “Sizing Structured UDT Columns” on page 715 for details about the composition and sizing of structured UDTs.
Use “Equation: Structured UDT Size for Packed64 Format System” on page 718 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 694), 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.
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 718. |
|
ARRAY/VARRAY |
|
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.