Kbase P116255: Performance implications - When to use idxcompact instead of idxbuild?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/08/2008 |
|
Status: Verified
GOAL:
When to use idxcompact instead of idxbuild?
GOAL:
Performance tuning benefits of idxcompact verses idxbuild
GOAL:
Performance tuning considerations when determining whether to use idxcompact or idxbuild to compress indexes.
GOAL:
Is there a utility to allow me to compress my indexes online?
GOAL:
Which utility should I use to compress my indexes? Idxcompact or idxbuild?
GOAL:
How to compress indexes online?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
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. The utilities are: idxcompact or idxbuild. These utilities are command line options to the proutil command. The following is a brief overview of the utility and the command used to invoke the utility.
idxcompact:
Performs index compaction online and increases space utilization of the index blocks to a specified compaction percentage. That is, the percentage specified is the amount of the index block that will be available to idxcompact for index data; the rest 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.)
idxbuild:
Consolidates index records to use disk space as efficiently as possible. That is, 100% of the index block is available for Index data. Activates deactivated indexes in the database. Repairs index corruption.
Command syntax:
proutil dbname -C idxbuild (Refer to the Database Administration Guide for command line options as they vary depending upon Progress version being used.)
Index compaction is done to reduce the number of blocks in a B-tree and possibly the number of B-tree levels, which improves query performance. Determining when and what utility to use to compact indexes is a very important DBA (Database Administrator) task. Selecting the wrong utility could negatively impact performance. Progress recommends the compaction of indexes when the space utilization of an index is reduced to 60% or less. The dbanalys utility can be used to determine the space utilization of an index.
Command syntax:
proutil dbname -C dbanalys > outputfilename
The utility you use to compact indexes indicates the degree of index compaction that will occur. Idxcompact allows you to specify the level of index compaction. Idxbuild does not. Idxbuild will compress indexes as tight as possible. Leaving little or no free space for expansion. Compacting an index tightly is fine for tables that contain static information. However, if you compact an index with no space for expansion for a table in which records are regularly added and deleted, you may actually cause performance degradation. 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.
Idxcompact by default compacts an index to 80% of available space in a block. This means that there is space left within the index blocks to accommodate the new keys associated with the new records. This allows for growth within the B-tree. Idxcompact also has the advantage that it can be run online or offline. Idxbuild however is an offline only utility. This means that the database would need to be shutdown prior to executing the idxbuild utility.
The benefits of using idxcompact to compress indexes make it the more desirable utility when compressing indexes on tables that are not static by nature.