Storage optimization

Data compression and consolidation processes can minimize the disk space that is used by your data and indexes.

The following table describes the processes that you can use to reduce the amount of disk space that is used by data in rows, simple large objects in dbspaces, and index keys. You can automate any or all of these processes or do them as needed.

Table 1. Storage optimization processes
Storage optimization process Purpose When to use
Compressing Compresses data in tables and fragments, compress simple large objects in dbspaces, and compresses keys in indexes. Reduces the amount of required disk space.

After you enable compression, new data or index keys is automatically compressed.

 When you want to reduce the size of 2000 or more rows of data, simple large objects in dbspaces, or 2000 or more index keys
Repacking Consolidates free space in tables, fragments, and indexes. After you compress or when you want to consolidate free space
Shrinking Returns free space to the dbspace. After you compress or repack or when you want to return free space to the dbspace
Defragmenting Brings data rows or index keys closer together in contiguous, merged extents. When frequently updated tables or indexes become scattered among multiple non-contiguous extents

The following illustration shows uncompressed data that uses most of the space in a fragment, free space that is created when the data is compressed, free space that is moved to the end of the fragment after a repack operation, and data that remains in the fragment after a shrink operation. The process for storage optimization of indexes is the same.

Figure 1. Data in a fragment during the compression and storage optimization process
This figure shows blocks of uncompressed data, blocks of compressed data with some blocks that have free space, blocks of repacked data with free space at the end of the fragment, and blocks of data after the free space is returned to the dbspace.