Interactions Between Compression and Other alter table Parameters

When a command requires data movement, the SAP ASE server compresses any uncompressed data rows in the source partitions if the target partition is compressed. When you include a compression clause, alter table includes these interactions between the parameters.

  • set – you:
    • Cannot combine set with add, drop, or modify clauses.

    • Cannot combine the modify partition set clause with other modify column_name parameters.

    • Cannot use the all keyword with modify partition and include partition names in the clause.

  • add:
    • You may add nullable and non-nullable compressed columns to existing tables. Adding non-nullable columns requires data movement.

    • Columns added to a compressed table use row compression if they are configured for an appropriate datatype.

    • Modifying a column’s datatype to one that is eligible for row compression in a compressed table does not change the column’s compression level.

    • If you do not specify not compressed for a new column, it inherits the table’s compression level. For example, if the table is row-level compressed, any column you add to the table uses row-level compression as well.

  • drop:
    • Dropping a compressed column causes data movement.

    • If the other columns in a table or partition are not compressed, or cannot be compressed, you must change the compression state to none before dropping the last compressed column.

  • modify:
    • You can modify a compressed column in an existing table to not compressed and vice versa.

    • You can change a column’s datatype simultaneously with its compression level. However, the target datatype must be eligible for row compression. If the target datatype is not eligible for compression, the SAP ASE server ignores the request to compress it during the modify operation.

    • The SAP ASE server issues an error if you attempt to modify the compression level of a column that you cannot create as a compressed column. For example, the SAP ASE server issues an error message if you attempt to compress a virtual computed column.

  • Combining add, drop, and modify:
    • You can issue multiple add, drop, or modify parameters that include one or more compressed columns in a single alter table statement. The data movement for this alter table command is dictated by the data movement restrictions for the parameters.

    • If alter table requires data movement, the column’s compression level remains unchanged for the columns not affected by the add, drop, or modify parameters.

  • Repartitioning a table – if you do not specify the compression clause for new partitions, the SAP ASE server sets the compression level for the new partitions as:
    • Uncompressed if the source table and all of its partitions are uncompressed.

    • The same compression level as the source table, if all of its partitions are compressed with the same compression level.

    • Uncompressed if:
      • The table or the individual partitions are compressed differently, or,

      • The source table is not compressed, but some of its partitions are

      The SAP ASE server does not compress the new partitions because it may be difficult to uniquely map and migrate the compression attribute from the original compressed partitions to the new partitions. You must explicitly state the compression level as part of the alter table command to specify which target partitions must be compressed during the data copy.

  • add partition – newly added partitions inherit the table’s compression level if you do not specify a partition level in the compression clause.

  • drop partition – dropping a table’s only compressed partition does not change the table’s compression level if the table includes multiple partitions.

    If a table has been defined for compression, it remains compressed after the partition is dropped, and the SAP ASE server automatically configures future partitions for compression.

  • Changing the locking scheme – the SAP ASE server requires data movement if you change the locking scheme of a table from allpages-locked to data-only-locked, or vice-versa. You cannot simultaneously change the compression level of the table or individual partitions when you change the locking scheme. Instead, you must run the set compression command to specify the compression level before you change the locking scheme.

  • Unpartitioning a table – if at least one source partition is compressed when you unpartition a table, the entire table is marked as compressed when you run alter table. The SAP ASE server issues a warning message if the table was initially uncompressed.

  • Other commands – you cannot combine parameters that specify the default value of a column, enable or disable triggers, add or drop column-level or table-level constraints, and so on, with commands that specify column-level or partition-level compression or copy data.

  • If alter table does not include data movement, existing data is not affected, and the SAP ASE server applies the table’s compression level to data once it is inserted. If alter table includes data movement, existing data is compressed or decompressed according to the table’s compression level.

  • These alter table events include data movement:
    • Adding non-null columns

    • Dropping columns

    • Modifying a column to increase its length (for example, from a smallint to an int, or from char(3) to varchar(45))

  • These alter table events do not include data movement:
    • Adding null column

    • Adding null text column (adding a non-null text column has restrictions)

    • Modifying a variable-length column to increase its length (for example, from varchar(5) to varchar(20) or from varchar(20) to varchar(40))

  • You cannot compress proxy tables or partitions or columns on proxy tables.