This section summarizes the benefits, best uses, and restrictions of algorithmic compression in Vantage.
Benefits of Algorithmic Compression
- Easy to define
- Can be specified for permanent, global temporary, and volatile table columns
- Compresses compressible columns in all table rows regardless of their value
When combined with multivalue compression for the same column, algorithmic compression only compresses the values that are not compressed by multivalue compression
- Has no effect on table header size
- Spools retain the algorithmic compression of their base tables
- No limit to the number of table columns that can be compressed using algorithmic compression
- No limit on the size of algorithmically compressed values
- Useful for columns with many unique or nearly unique values
- Easily and complementarily combined with multivalue compression
- Easily combined with autocompression for column-partitioned tables and join indexes
- Can specify algorithmic compression on a secondary index column, though the column data in the secondary index subtable is not algorithmically compressed
- Can specify algorithmic compression on permanent and global temporary table columns
- Automatically compresses nulls
Best Use of Algorithmic Compression
- Very wide character columns, particularly Unicode columns
- Columns that contain click stream data
Restrictions and Limitations of Algorithmic Compression
- Both compression and decompression consume CPU resources, causing negative performance issues
Performance issues are restricted to when an algorithmically compressed column is referenced directly
- If you use your own compression algorithms, you must write and test your own compression UDFs to implement them
- Can be used for only a few data types
- Compressed values are stored within the row
- Best not used in combination with block-level compression because of the combined cost of their decompression
- Cannot specify for primary index or primary AMP index columns
- Cannot specify for the partitioning columns of a partitioning expression