Storage optimization methods

You can optimize individual tables, fragments, or indexes. You can schedule the automatic optimization of all tables and fragments.

You can use the COMPRESSED option in the CREATE TABLE statement to enable automatic compression of the table when the table has at least 2000 rows.

You can use the COMPRESSED option in the CREATE INDEX statement to enable automatic compression of the index if the index has 2000 or more keys. Compression is not enabled if the index has fewer than 2000 keys.

You can use the SQL administration API task or admin function to perform any type of storage optimization on a table, fragment, or index.

You can enable the auto_crsd Scheduler task to automatically compress, repack, shrink, and defragment all tables and table fragments.

Table 1. Methods of storage optimization
Goal SQL statement SQL administration API argument Scheduler task OAT page
Automatically compress data for a table or fragment CREATE TABLE with the COMPRESSED option table compress or fragment compress   Storage
Automatically compress data for all tables and fragments     auto_crsd Server Optimization Policies
Repack and shrink a table or fragment   table repack shrink or fragment repack shrink   Storage
Automatically repack and shrink all tables and fragments     auto_crsd Server Optimization Policies
Automatically compress a B-tree index CREATE INDEX with the COMPRESSED option index compress   Storage
Repack and shrink a B-tree index   index repack shrink   Storage
Defragment a table of fragment   defragment   Storage
Automatically defragment all tables and fragments     auto_crsd Server Optimization Policies

Copyright© 2019 HCL Technologies Limited