How to improve IDXCOMPACT to compact indexes better during it's first run?

How to improve IDXCOMPACT to compact indexes better during it's first run?
IDXCOMPACT does not always compact indexes completely and requires another run before indexes are compacted
IDXCOMPACT does not compact a unique or Table Partitioning global unique index to the % specified

After deleting many records with unique indexes IDXCOMPACT needs to be run another time before indexes are compacted as required, 

Example: A unique index (without partitioning). After deleting +/- 16 million records, it takes 4s to read this empty table (with nothing in it).

IDXANALYS shows 3x idxcompact to eventually clean delete placeholders:

Table  Index Fields Levels  Blocks  Size   % Util Factor
idx1      31     1       3   26664  101.4M   98.1    1.0
idx1      31     1       3    6616   25.2M   98.2    1.0  # IDXCOMPACT Run1
idx1      31     1       2       2    3.9K   49.2    2.0  # IDXCOMPACT Run2

Example: Table Partitioning global unique index, each time the size and % Util decreased, but blocks remains the same:

Table      Index Fields Levels   Blocks  Size   % Util Factor
idxSeq:0      24      1      3     4510 17.2M     98.2    1.0
idxSeq:0      24      1      3     4451  6.1M     35.5    2.3   # IDXCOMPACT Run1
...
idxSeq:0      24      1      3     4451 121.7K     0.7    3.0   # IDXCOMPACT Run(n)

Table      Index Fields Levels   Blocks  Size % Util Factor 
idxSeq:0      24      1      3     4510 17.2M   98.2    1.0
idxSeq:0      24      1      1        1 46.0B    1.1    1.0    # IDXBUILD

 

It is not unusual for index trees to be GT level 2 on larger databases, result in the tree not being compacted as expected in the first run. Specifically for unique indexes that rarely have had idxcompact run or those who's associated record data are mass created then deleted,

1. When an index block is removed from the tree due to delete place cleanup, index compact process might leave the branch (Called "A") of the tree and move on to the rest of the tree. The branch A is not properly compacted or cleaned up yet. Index compact itself will redo the processes several times (passes) to address this. However, when there are lots of blocks full of delete holders, the current algorithm is not efficient enough to take care all of them. This is why idxcompact produces a different outcome each time when the tree is full of blocks with delete holders.
2. Global index with index compact has another minor issue in the current algorithm with delete holders, when representing the blocks used for the partition in idxanalys reports.

Upgrade to OpenEdge 12.5, 12.2.7, 11.7.11 where OCTA-37121:

1. Fixed blocks used for the partition in idxanalys reports.
2. Enhanced the IDXCOMPACT algorithm so that deleted blocks are more efficiently removed from the tree in a run.

IDXCOMPACT runs two phases:

First phase: idxcompact scans the index delete chain and moves these blocks to the free chain when possible.

• The degree of index compaction is not used at this phase as only the information in block header is changing,
• idxcompact creates a separate transaction for each block on the index delete chain, bi notes are small.

Second phase: idxcompact scans remaining blocks of the index tree.

• The degree of index compaction % is used in the second phase
• In one transaction to facilitate undo processing if interrupted, bi notes are larger.

The index compact mechanism itself was changed when cleaning up delete holders, so that delete holders are more efficiently cleanedup with idxcompact. This second change benefits all OE databases and their unique indexes, not just TP and global indexes.

In prior versions it may be necessary to run IDXCOMPACT more than once.

Progress Articles:
IDXCOMPACT to only empty the index delete chains after mass purge on records with unique indexes   
Parallel IDXCOMPACT performance degradation
Database crash on IDXCOMPACT 1124 crash recovery fails