Neither of these utilities is better than the other, they both accomplish specific functions.IDXCOMPACT:
- Performs index compaction and increases space utilization of the index blocks to a specified compaction percentage.
- Is a subsection of the larger IDXBUILD, it performs the index block consolidation phase of IDXBUILD. In addition to compacting an index, it also clears dead entries left after entries have been deleted from unique indexes, like IDXBUILD which builds indexes from scratch.
- Can be run either offline or online. When run online, it 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 operation, it may however compete for system resources which may degrade performance for other clients sessions.
- Online IDXCOMPACT can be run in parallel, but when run against indexes of the same table it will be slower. For further information refer to Article Parallel IDXCOMPACT performance degradation
- No other administrative operation on the index is allowed during the compacting process.
- Cannot correct index corruption.
- Cannot activate inactive indexes.
IDXBUILD:
- Consolidates index records to use disk space as efficiently as possible
- Can correct index corruption.
- Will activate inactive indexes.
- Cannot be run online.
If index block consolidation is all that is required, consider IDXCOMPACT.
If index repair or activation as well as index block consolidation are needed then consider IDXBUILD.
