index compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API)
Use the index compress repack shrink argument with the admin() or task() function to compress detached B-tree indexes, consolidate free space (repack), and return free space (shrink) in partitions.
Command arguments
The following table contains a brief explanation of each argument.
| Argument | Description |
|---|---|
| compress | Compresses the index. |
| parallel | Runs the compress or repack operation in parallel. A thread is started for each fragment of the table or fragment list and the operation is run in parallel across those fragments. |
| repack | Consolidates free space by moving data to the front of the index. |
| shrink | Returns free space at the end of the index to the dbspace, thus reducing the total size of the index. |
Command elements
The following table shows the elements that you can use to compress, repack, and shrink indexes.
| Element | Description | Key Considerations |
|---|---|---|
| index_name | The name of the index that you want to compress. | Required. You must use the same uppercase or lowercase letters that are in system catalog tables. |
| database_name | The name of the database that contains the index that you want to compress. | Optional. If you do not specify a database, the database server uses the current database. If you enter a database name, you must use the same uppercase or lowercase letters that are in system catalog tables. |
| owner | The name of the owner of the database that contains the index that you want to compress. | Optional. If you do not specify an owner, the database server uses the current owner. If you enter an owner name, you must use the same uppercase or lowercase letters that are in system catalog tables. |
Usage
You can compress a detached B-tree index that is on a fragmented or non-fragmented table. You cannot compress an attached index.
To be compressed, an index must have at least 2000 keys. If a fragment within the index does not have at least 2000 keys, the database server does not compress the index or fragment when it creates the index. The index remains uncompressed even if new keys are added to it. If you want to compress the index, run another SQL Admin API task() or admin() function with the index compress argument.
To determine if an index contains the minimum number of keys, run the oncheck -pT command and view information in the Number of keys field.
Typically you perform a repack operation after a compress operation and the shrink after a repack operation.
The compression operation compresses only the leaves (bottom level) of the index.
You can cancel a command, for example, by typing CTRL-C in DB-Access.
You cannot uncompress an index. If you want an uncompressed index, you can drop the compressed index and recreate it.
Example
The following command compresses, repacks, and shrinks an index in parallel.
EXECUTE FUNCTION task("index compress repack shrink parallel",
"ind5", "customer", "jayson");