When you answer "no" to sorting for an index rebuild:
A non-sorted index rebuild effectively rebuilds the index from scratch, without sorting.
A non-sorted index rebuild replaces the index records in the same way a regular insert is done when creating/updating index key fields, complete with block splits and empty spaces.
For this reason, when building indexes on existing tables, it is recommended to run an IDXCOMPACT
When you answer "yes" to sorting for an index rebuild,
Blocks are put into a sort file and then moved back several at a (-TM) time. This provides the following benefits:
- The blocks are closer together.
- The index manager regulates how full the blocks are before a split operation is required
- The speed of both retrieval and insertion of records is improved, as well as the speed of the index rebuild itself.
-TMB : Merge Block Size since 10.2B06, facilitates specifying the size of the blocks used to
merge sorted blocks together, separately from the
Sort Block Size (-TB). As such, the default/minimum value for -TMB is the -TB value. If -TMB is set to a value less than -TB, index rebuild will exit.
Use of the -TB option with the index rebuild utility improves performance at the cost of disk space while use of the -TM option improves performance at the cost of memory usage. The -TF memory percentage parameter facilitates better in memory operations:
What is the -TF Option used with PROUTIL IDXBUILD?
These parameters are specified in PROUTIL idxbuild command, as follows:
$ proutil <dbname> -C idxbuild -TM 32 -TB 64 -TMB 128 -TF 50