This example presents row structure cases based on the following table definition. This table has multivalue compression defined on columns mvc_f1 and mvc_v1, and algorithmic compression defined on column alc1. Particularly relevant data for each case is bold.
CREATE TABLE t1( fc1 INTEGER, alc1 VARCHAR(10) COMPRESS ALGCOMPRESS huffcomp ALGDECOMPRESS huffdecomp, vc1 VARCHAR(20), mvc_f1 CHARACTER(10) COMPRESS (‘Germany’,’France’,’England’), mvc_v1 VARCHAR(20) COMPRESS (‘Nike’,’Reebok’,’Adidas’) );
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 14 0 0 Var Nullable 0 5 NonKey DBC VARCHAR(20) LATIN 4 1028 - 5 226 Comprs cmp+Null2 0 6 NonKey DBC VARCHAR(10) LATIN 5 1029 - 3 256 Comprs cmp+Null2 1 1 NonKey DBC VARCHAR(20) LATIN
Case 1
No compression has been specified for the data values, so data for columns alc1, mvc_f1, and mvc_v1 is stored in the row.
The following abbreviations are used for the fields of the diagram for this case.
| Abbreviation | Definition |
|---|---|
| fc1 | Uncompressed data for column fc1, a fixed length data type. |
| len1 | Compressed length of the data in column alc1, an algorithmically-compressed variable length data type. |
| alc1 | Algorithmically-compressed data for the variable length column alc1. |
| mvc_f1 | Multivalue compressed data for the fixed length column mvc_f1. |
| len2 | Length of the data in column mvc_f1, a multivalue compressed variable length data type. |
| mvc_v1 | Multivalue compressed data for the variable length column mvc_v1. |
| vc1 | Uncompressed data for column vc1, a variable length data type. |
Vantage has placed the variable compressible columns after the fixed field columns in the compressible columns area. This is also reflected in the Field5 descriptor. The compressed column data is interleaved with other compressible fields. len1 and len2 contain the uncompressed lengths of alc1 and mvc_v1, respectively.
Case 2
Vantage compresses the data for columns mvc_v1 and alc1 because the values for the row are specified in the multivalue for those columns.
The following abbreviations are used for the fields of the diagram for this case.
| Abbreviation | Definition |
|---|---|
| fc1 | Uncompressed data for column fc1, a fixed length data type. |
| len1 | Compressed length of the data in column alc1, an algorithmically-compressed variable length data type. |
| alc1 | Algorithmically-compressed data for column alc1, a variable length data type. |
| mvc_f1 | Multivalue compressed data for column mvc_f1, a fixed length data type. |
| mvc_v1 | Not represented. This column is compressed, so no value for mvc1 is stored in the row. |
| vc1 | Uncompressed data for column vc1, a variable length data type. |
The database does not store the value for mvc_v1 in the row because it is compressed for the data value, so the value is stored in the table header. len1 contains the compressed length for alc1.
Case 3
The data for the compressible columns mvc_v1 and alc1 is null, so the database does not store values for those columns in the row. This example demonstrates that nulls never incur a cost overhead for compression.
The following abbreviations are used for the fields of the diagram for this case.
| Abbreviation | Definition |
|---|---|
| fc1 | Uncompressed data for column fc1, a fixed length data type. |
| len1 | Not represented. This column is null, so no value for alc1 is stored in the row. |
| alc1 | Not represented. This column is null, so no value for alc1 is stored in the row. |
| mvc_f1 | Multivalue compressed data for column mvc_f1, a fixed length data type. |
| len2 | Not represented. This column is null, so no value for mvc_v1 is stored in the row. |
| mvc_v1 | Not represented. This column is null, so no value for mvc_v1 is stored in the row. |
| vc1 | Uncompressed data for column vc1, a variable length data type. |