You can compress data at the column level using multi-value compression, a lossless, dictionary-based compression scheme. With MVC, you specify a list of values to be compressed when defining a column in the CREATE TABLE/ALTER TABLE statement. When you insert a value into the column which matches a value in the compression list, a corresponding compress bit is set instead of storing the actual value, thus saving the disk storage space.
The best candidates for compression are the most frequently occurring values in each column. MVC is a good compression scheme when there are many repeating values for a column.
If MVC is not an efficient compression scheme for data in a particular column, you can compress the column using algorithmic compression (ALC). You can specify MVC alone, or both MVC and ALC on the same column. If you define both on the same column, ALC is applied only to those non-null values that are not specified in the value compression list of the MVC specification. You can also use MVC together with block-level compression (BLC).
- Any numeric data type
- DATETo compress a DATE value, you must specify the value as a Date literal using the ANSI DATE format (DATE 'YYYY-MM-DD'). For example:
COMPRESS (DATE '2000-06-15')
- TIME and TIME WITH TIME ZONE
- TIMESTAMP and TIMESTAMP WITH TIME ZONE
COMPRESS (TIME '15:30:00') COMPRESS (TIMESTAMP '2006-11-23 15:30:23')
- Non-LOB distinct or structured UDT