Example: Multivalue Compression But No Algorithmic Compression - Teradata Vantage

This example presents row structure cases based on the following table definition. This table has multivalue compression defined on columns mvc1 and mvc2, but no algorithmic compression. Particularly relevant data for each case is bold.

     CREATE TABLE t1 (
       fc1  INTEGER,
       mvc1 VARCHAR(10) COMPRESS ('mars','saturn','jupiter'),
       vc1  VARCHAR(20),
       mvc2 VARCHAR(10) COMPRESS ('Germany','France','England'),
       vc2  VARCHAR(20) );

Field 5 of the table header for this table has the following field descriptors.

                         UDT or
              Num-  Next Com-                   Pres-
In- Fld  Off- Comp- Fld  press  Calc   Sto-     ence     Sort
dex id   set  ress  Ind  offset offset rage     Byte Bit Desc   EvlRepr
--- ---- ---- ----- ---  ------ ------ -------- ---- --- ------ ------------------
 1  1025  20         2     0    Offset Nullable   0   1  AscKey DBC INTEGER
 2  1026  -          4    196   Comprs cmp+Null2  0   2  NonKey DBC VARCHAR(10) LATIN
 3  1027  14         5     0    Var    Nullable   0   5  NonKey DBC VARCHAR(20) LATIN
 4  1028  -          3    206   Comprs cmp+Null1  0   6  NonKey DBC VARCHAR(10) LATIN
 5  1029  16         0     0    Var    Nullable   0   1  NonKey DBC VARCHAR(20) LATIN

Case 1

The data values for columns mvc1 and mvc2 are both stored in the row because they are not compressed for the data values they contain.

Vantage has placed the variable length compressible columns after the fixed length columns in the compressible columns area of the row. This is also reflected in the Field5 descriptor. len1 and len2 contain the uncompressed lengths of those columns.

The following abbreviations are used for the fields of the diagram for this case.

Case 2

The next case demonstrates row storage when the column data in the row is to be multivalue compressed. The value for column mvc1 is not stored in the row because it is compressed. The value is instead stored in the table header and referenced by the presence bits array for the row.

The following abbreviations are used for the fields of the diagram for this case.

Case 3

The next case demonstrates row storage when the column data for mvc1 is null. The value for column mvc1 is not stored in the row because nulls are compressed. This demonstrates that nulls never incur a cost overhead for compression.

The following abbreviations are used for the fields of the diagram for this case.