A feature enhancement in OpenEdge 10.1C to the PROUTIL -C IDXBUILD utility was the 'packing factor' -pfactor <%> to use when building indexes on 8KB databases.
When IDXBUILD rebuilds large indexes with 100% compaction, an initial performance penalty is realised as the B-tree needs to split levels to expand. When index compaction is 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.
- IDXCOMPACT on the other hand will never ‘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. IDXBUILD compacts the index at 100% by default pre 10.1C and variable compaction specified with the -pfactor parameter post 10.1C only on databases with 8KB blocksize. Otherwise, the -pfactor is ignored and uses the 100% default idxbuild packing factor to build indexes.
To verify the IDXBUILD packing factor:
A. The database.lg file can be parsed for message:
(13943) TB value is 24, TM value is 32, SG value is 64, packing factor is 80
B. Index analysis reports will show that no index utilization % is above the number provided for -pfactor
$ proutil -C idxanalys [Area_Name]
$ proutil -C idxblockreport <TableName.IndexName>
Example: After building the customer.name index with a packing factor of 80, the "Percent Utilised" are all below the -pfactor used.
$ proutil dbname -C idxblockreport Customer.name
BlockSize = 8192 Block Capacity = 8192
Number Length On Length Delete
of of Delete of Chain Percent
DBKEY Level Entries Entries Chain Size Type Utilized
512 1 4 50 0 0 root 0
320 2 320 6530 0 0 leaf 79
352 2 296 6526 0 0 leaf 79
288 2 341 6531 0 0 leaf 79
256 2 161 3331 0 0 leaf 40
Index Block Statistics reported successfully.
