Example: Algorithmic Compression But No Multivalue Compression - Teradata VantageCloud Lake

Suppose you define the following table to use Huffman encoding algorithms to compress and decompress two of its columns algorithmically. This table specifies algorithmic compression on columns alc1 and alc2, but no multivalue compression. Particularly relevant data for each case is bold.

     CREATE TABLE t1 (
       fc1  INTEGER,
       alc1 VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp
                                 ALGDECOMPRESS huffdecomp,
       vc1  VARCHAR(20),
       alc2 VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp
                                 ALGDECOMPRESS huffdecomp,
       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 alc+Null1  0   2  NonKey DBC VARCHAR(10) LATIN
 3  1027  12         5     0    Var    Nullable   0   4  NonKey DBC VARCHAR(20) LATIN
 4  1028  -          3    206   Comprs alc+Null1  0   5  NonKey DBC VARCHAR(10) LATIN
 5  1029  14         0     0    Var    Nullable   0   7  NonKey DBC VARCHAR(20) LATIN

To be as general as possible, each row structure diagram indicates the configuration of fields as if the table has a partitioned primary index, though none of the actual table creation SQL text specifies a PPI. The diagrams also assume the system has a Packed64 row structure.

Case 1

Suppose you have the same table, but without any algorithmic compression being defined. The row structure for this table looks something like the following diagram.

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

The data values for columns alc1 and alc2 are both stored in the row and are not compressed.

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. Because the columns are not compressed, the database stores lengths with the two alc columns. len1 and len2 contain the uncompressed lengths of those columns, and the ALC bit is not set.

Case 2

In the next case, algorithmic compression has been defined, and the data for columns alc1 and alc2 is not null. alc1 and alc2 are present in the row and compressed. len1 and len2 contain the new compressed length. The ALC bit has been set for this case to indicate that the data in columns alc1 and alc2 is compressed.

For this case, the row structure for the table superficially looks exactly like the row structure diagram for the previous case.

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

Case 3

In the next case, algorithmic compression has been defined, but the data for columns alc1 and alc2 is null. alc1 and alc2 are present in the row and compressed. len1 and len2 contain the new compressed length. The ALC bit is not set for this case because column alc1 is null.

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