You can compress nulls and up to 255 distinct values per column. You can compress an unlimited number of columns per table. This value is constrained by the maximum system row length because compressed values are added to the table header row for each column.
| Type of Multivalue Compression | Description |
|---|---|
| Single-valued | Default is null if no value is specified. |
| Multivalued |
|
You can add a new column with multivalued compression to an existing table, add multivalued compression to an existing column, or you can drop compression from an existing column by specifying the NO COMPRESS attribute.
If a column is constrained as NOT NULL, then none of the specifications in the compression list can be the literal NULL.
Columns defined with the COMPRESS attribute cannot participate in fast path INSERT … SELECT operations. If you use INSERT … SELECT on a target table with multivalue compressed columns, the Optimizer does not specify fast path optimization for the access plan.
The performance cost of being unable to use fast path INSERT ... SELECT is typically offset by the performance advantages of multivalue compression.
- Identity
- LONG VARCHAR
- BLOB
- CLOB
If the data type of any column in the new table is not compatible with the value of the corresponding field in the existing table, individual INSERT requests must be used to load each row.
If all the new data types are compatible with all the existing values (for example, only the COMPRESS attribute is being changed), you can use an INSERT … SELECT request to copy all the rows in a single request.
Also see the "Adding or Modifying Multivalue Compression for a Column" section in Using Compression Methods with Table Columns.
See Multivalue Compression for more detailed information about multivalue compression and its applications for performance optimization and disk capacity savings.