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.

Read syntax diagramSkip visual syntax diagram
Syntax: Index compression command arguments

>>-EXECUTE FUNCTION--+-admin-+--(--"--index--------------------->
                     '-task--'                

>--+-compress--+--------+--+--------+--+----------+-+--"-------->
   |           '-repack-'  '-shrink-'  '-parallel-' |      
   +-repack--+--------+--+----------+---------------+      
   |         '-shrink-'  '-parallel-'               |      
   '-shrink-----------------------------------------'      

>--,--"index_name"--,--"database_name"--,--"owner"--)--;-------><

Command arguments

The following table contains a brief explanation of each argument.

Table 1. Arguments for index compression operations
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.

Table 2. Index compress command elements
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");

Copyright© 2018 HCL Technologies Limited