Learning about table compression and defragmentation
Inefficient use of disk space can degrade performance. If space is poorly utilized (deletes can free up a lot of row space), the I/O will increase for a given data set retrieval. When table pages are distributed across many extents, more seek time is needed to find the data. You can repack and shrink independently of compression. The repack consolidates free space, and the shrink returns free space to the dbspace, which shortens extents as much as possible. Repack can be performed while the engine is offline.
Defragmentation resolves the second problem by relocating and merging extents wherever possible.
Table compression and defragmentation are performed through the administrative API. Following are some examples of administration API commands:
Listing 30. Repack API command
EXECUTE FUNCTION task("table repack", "table_name", "database_name", "owner_name");
Listing 31. Shrink API command
EXECUTE FUNCTION admin("table shrink", "table_name", "database_name", "owner_name");
Listing 32. Defragment API command
EXECUTE FUNCTION task("defragment", "database_name:owner_name.table_name");
You can administer these operations using the Open-Admin Tool (OAT), and they can be scheduled.