8. SQL Statements : MODIFY : With Clause Options for Modify : Compression
 
Share this page                  
Compression
To specify data and key compression, use the WITH COMPRESSION clause. Compression removes the string trim from variable character data. The following table lists valid compression options:
Storage Structure
Base Table or
Secondary Index
Can Compress Data?
Can Compress Key?
Hash
Base Table
Yes
No
 
Secondary Index
Yes
No
Heap
Base Table
Yes
No
 
Secondary Index
No
No
Btree
Base Table
Yes
Yes
 
Secondary Index
No
Yes
Isam
Base Table
Yes
No
 
Secondary Index
Yes
No
To specify an uncompressed storage structure, specify WITH NOCOMPRESSION.
To compress both key and data for tables where this is valid (primarily btree), specify WITH COMPRESSION, omitting the KEY and DATA clause. To compress data or keys independently of one another, specify WITH COMPRESSION = (KEY|DATA). To compress data using bit compression, specify WITH COMPRESSION = HIDATA. To explicitly suppress compression of data or keys, specify WITH COMPRESSION = (NOKEY | NODATA).
If not specified, modify to storage-structure removes compression, unless the c-prefix variants are used (cbtree and so on). Other variants of MODIFY preserve the table's compression type.
If a secondary index is compressed (with KEY compression), the non-key columns will also be compressed.