The benefits of using IDXCOMPACT to compress indexes make it the more desirable utility when compressing indexes on tables that are added to or updated regularly. The following discusses why.
There are two utilities that can be used to compress indexes in a database:
- PROUTIL IDXCOMPACT, or
- PROUTIL IDXBUILD.
The following is a brief overview of each utility and the command used to run each. For specific details please refer to the
OpenEdge Data Management:Database Administration Documentation.
1. IDXCOMPACT:Performs index compaction online or offline and increases space utilization of index blocks to a specified compaction percentage. The percentage specified is the amount of the index block that will be available to IDXCOMPACT for index data; the remainder of the block will be organized as empty space.
Command syntax:
$ PROUTIL dbname -C IDXCOMPACT [owner-name.]table-name.index-name [n]
The value of n specifies the degree of index compaction and must be an integer >=50 and <=100. The default value is 80.
2. IDXBUILD:Can only be run offline. IDXBUILD consolidates index records to use disk space as efficiently as possible. That is, 100% of the index block is available for Index data.
Command syntax:
$ PROUTIL dbname -C IDXBUILD [+params ...]
Please refer to the Database Administration Guide for command line options as theses vary depending upon Progress OpenEdge version in use.
When to Compact Indexes?Determining when and which utility to use to compact indexes is a very important DBA (Database Administrator) task. Progress recommends that indexes need compacting when the space utilization of an index is reduced to 60% or less. The DBANALYS or specifically: IDXANALYS utility can be run either online or offline to determine the current space utilization of an index.
Command syntax:
$ PROUTIL dbname -C idxanalys [area <area-name] > outputfilename
How Compact to make the index?Both IDXBUILD and IDXCOMPACT compact indexes reducing the number of blocks in a B-Tree and possibly the number of B-Tree levels, which improves query performance. The utility used to compact indexes affects the degree of index compaction that will occur.
IDXCOMPACT allows the level of index compaction to be specified between 50 – 100%.
IDXBUILD Pre OpenEdge 10.1C:
- IDXBUILD does not allow the degree of compaction to be specified. IDXBUILD will compress indexes as tight as possible (100%), leaving little or no free space for expansion.
- While compacting an index tightly is good for tables that contain static information, compacting an index with no space for expansion on a table in where records are regularly added, updated and deleted, will cause performance degradation immediately after the compaction.
- The degradation in performance is the result of index block splits which will now have to occur within the B-tree to accommodate the new keys. These index block splits can occur on various levels within the B-tree and can negatively impact performance as there is an overhead in B-Tree expansion.
IDXBUILD Since OpenEdge 10.1C:
- For this reason, a feature enhancement to the IDXBUILD utility was the 'packing factor' -pfactor <%> in OpenEdge 10.1C.
- This packing factor is similar to the IDXCOMPACT compaction value, which allows the maximum percentage of space used in an index block to be changed from the default of 100%, between values GT 60 and LT 100.
- However pfactor only comes into effect if the database blocksize is 8KB due to 'largekeys enablement' since 10.2B, that prevents IDXBUILD to use anything but 100% in smaller blocksizes. In otherwords, IDXBUILD can only compact indexes less than 100% on databases with 8 KB Blocksizes.
IDXCOMPACT vs IDXBUILD:IDXBUILD requires After-Imaging to be disabled, where IDXCOMPACT does not.
IDXCOMPACT can be run online or offline, whereas IDXBUILD is an offline only utility, which means that the database would need to be shutdown prior to executing the IDXBUILD utility. When IDXCOMPACT is run online:
- While no other administrative operation on the index is allowed during the compacting process, it does not lock any record or table during execution.
- IDXCOMPACT only locks one to three index blocks at a time, for a short time and there are no record or table locking during the execution. This allows full concurrency for other users whom can use the index simultaneously for read or write operations, it may however compete for system resources which may degrade performance for other clients sessions. It may however compete for system resources which may degrade performance for other clients sessions, but will not be responsible for bi growth.
- Online IDXCOMPACT can be run in parallel, but when run against indexes of the same table it will be slower. The reasons are explained in Article Parallel IDXCOMPACT performance degradation
- The benefits of using IDXCOMPACT to compress indexes online makes it the more desirable utility particularly when compressing indexes on tables that are not static by nature that need compacting to a higher value than they currently are at but under 100% to allow for expansion.
IDXCOMPACT allows the degree of index compaction to be specified between 50 and 100% of available space in a block. For compaction under 100%, this allows for growth within the B-tree as there is remaining space within the index blocks to accommodate the new keys associated with the new records. Whereas IDXBUILD compacts at 100% by default pre 10.1C and variable compaction post 10.1C only on databases with 8KB blocksize. IDXCOMPACT however, will not ‘uncompact’ an index. For example, if an index is already at 95%, it will not de-compact to 60% specified. IDXBUILD can be used to de-compact indexes.
IDXCOMPACT in addition to compacting an index, utility clears dead entries left after entries have been deleted from unique indexes. Since IDXBUILD does the same since it builds indexes ‘from scratch’. The advantage of IDXBUILD is that it additionally repairs index corruption and activates deactivated indexes, which IDXCOMPACT does not.
When to Run IDXBUILD or IDXCOMPACT?Refer to Article
Which is better IDXBUILD or IDXCOMPACT?
