Altering a Table’s Compression

Considerations for altering table compression.

  • Use set compression to change the compression level of the table for future data inserts or updates. set compression does not affect existing data rows and pages that are already compressed, but does require exclusive access to the table.

  • You cannot change a partition’s compression level in the same command in which you are altering a table’s compression level. You must perform these operations as independent commands

  • You may use set compression with other set parameters.

  • Changing the table’s compression level affects only those partitions that do not already have an explicitly defined compression level. All partitions without an explicitly defined compression level implicitly inherit the table’s compression level. For example, if you modify a table’s compression level from uncompressed to row-level compression, all partitions that had a compression level of none do not change, but partitions for which their compression level was undefined are changed to row-level compressed.

  • Altering a table’s compression level does not change the compression level for existing columns. For example, if my_table and its columns are uncompressed, when you alter the compression level of my_table, its columns initially remain uncompressed. However, the SAP ASE server compresses these columns individually when they fill with enough data to trigger the compression mechanism.

  • The default behavior for newly added columns depends on the table’s compression setting. For compressed tables, the column’s datatype determines its compression level. For uncompressed tables, new columns are uncompressed.

  • You may add compressed materialized computed columns to a table or compress them later.

Parent topic: alter table