Storage optimization methods
You can optimize individual tables, fragments, or indexes. You can schedule the automatic optimization of all tables and fragments. You can optimize individual tables or fragments. 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 use the SQL administration API task or admin function to perform any type of storage optimization on a table or fragment.
You can enable the auto_crsd Scheduler task to automatically compress, repack, shrink, and defragment all tables and table fragments.
| 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 |