Altering the Compression Level for a Table Using Large Objects

Changing the table’s large object (LOB) compression level affects only the LOB columns that do not have an explicitly defined compression level. Columns without an explicitly defined compression level implicitly inherit the table’s compression level.

The default behavior for newly added LOB columns for which you have not specified a LOB compression clause depends on the table’s LOB compression level. For LOB compressed tables, the SAP ASE server uses the table’s LOB compression level for the columns. For LOB uncompressed tables, newly added LOB columns remain uncompressed.

Interactions between compression and other alter table parameters for tables with LOB data:

  • drop column – if the table includes no compressed LOB columns after dropping columns, the table uses the table-level LOB column compression level.

  • add column

    • You can add a nullable compressed LOB column, but you cannot add a non-nullable compressed LOB column.

    • For a table not set to LOB compression, by default, newly added LOB columns are not compressed. Newly added LOB columns with LOB compression subclauses can be compressed or not compressed as specified.

  • modify column

    • You can uncompress an existing compressed LOB column. Although newly inserted data is uncompressed, existing data remains compressed.

    • You can change the compression level of an existing LOB column. Although newly inserted data assumes the new compression level, existing data retains the original compression level.

    • You can change an uncompressed LOB column to compressed.

    • You cannot modify a regular column to a LOB column (compressed or uncompressed).

    • You can modify a compressed LOB column to:

      • Compressed text columns using nchar, nvarchar, unichar, and univarchar

      • Compressed image columns using varbinary and binary

      • Compressed unitext columns using nchar, nvarchar, unichar, univarchar, varbinary, and binary

      Compressed off-row java columns cannot be modified to regular columns.

      The SAP ASE server decompresses the LOB data, truncating the data if necessary to fit the regular column length, and converting it to the regular datatype. The maximum length of the regular column is governed by the SAP ASE server page size.

  • Combinations of add, drop, modify, and set lob_compression:

    • You can issue multiple add, drop, or modify subcommands in a single alter table command—or set lob_compression and set compression subclauses—that involves one or more compressed columns.

    • If you add a column to a LOB-compressed table and include set lob_compression = 0 in the command, the newly added column is not compressed.

    • If you add a column to a regular, uncompressed table, and include set lob_compression = compression_level in the command, the newly added column is compressed.

    Existing LOB data is not affected by alter table commands; only future DMLs are affected by the changed LOB compression attributes. Use update and select into to compress or uncompress existing LOB data.

Parent topic: alter table