Kbase P103055: Why deleting records in a table leads to index fragmentation ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/02/2010 |
|
Status: Verified
GOAL:
Why deleting records in a table leads to index fragmentation ?
GOAL:
Why idxanalys shows large fragmentation after a large record delete?
GOAL:
When to run idxcompact?
GOAL:
Why are index entries occupying up to 10 times physical space?
GOAL:
Is it possible to view the contents of the index tree?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
When records are deleted, index-trees are not rebuilt. The entire B-tree is left intact. This was a change introduced in Progress 9.x, where index entry locks are not removed at the end of transaction. During a transaction, when a record is deleted, if there is a unique index, a delete place holder is put in the place of the RECID for the deleted record. At the end of the transaction, we convert it to a negative number and put it in place of the RECID in the index. i.e. Progress treats the index entry locks as index entries during subsequent read operations. Of course, it can't read these specified blocks because RECID is higher than any possible High Water Mark (HWM) for that area. But it's still a logical request. So having deleted a sufficient amount of records of a table in a short time span, would leave a situation of high fragmentation and quite possibly query performance issues. As a result, one of the two corrective actions below would need to be taken after such an operation to reduce the number of index blocks in an effort to increase index utilization. (performance.)
Ideally, an idxbuild operation on the affected table(s) would correct this immediately, but the utility has to be run offline. The "proutil -C idxcompact" operation on the other hand, can be run both online and offline to reduce the number of blocks in the B-Tree and possibly the number of B-Tree levels. (Note: It is not recommended that you run the idxcompact without getting to 10.1C04 or later, or 10.2A01 or later as there was a known issue that resulted in excessive bi growth due to the scope of the transaction being handled as a large transaction).
How the index compact would help matters is as follows:
1) If the index is a unique index, the delete chain is scanned and the index blocks are cleaned up by removing deleted entries.
2) The non-leaf levels of the B-tree are compacted starting at the root working toward the leaf level.
3) The leaf level is compacted.
In other words, in addition to compacting an index, this utility clears dead entries left after entries have been deleted from unique indexes.
Please note however, that there are a number of known issues with running the idxcompact utility online, that have been fixed in later service packs. It is strongly recommended that you get to 10.1C04 or later or 10.2A01 before running idxcompact online.
The command line argument for idxcompact is:
$ proutil dbname -C idxcompact tablename.indexname
The command line arguments can be created and then run through a shell script, for example, as follows:
<snip>
OUTPUT TO TABLE.FILE.
DEF VAR mydb AS CHAR FORMAT "X(10)" INITIAL "dbname".
For each _file where _file-number > 0 AND
NOT _file-name BEGINS "SYS":
for each _index OF _file:
DISPLAY "proutil" mydb "-C i.dxcompact "_file._file-name "."_index._index-Name " 80" WITH NO-LABELS NO-BOX WIDTH 126.
End.
END.
OUTPUT CLOSE.
DISPLAY "file is ready for editing".
</snip>
** As with running any database utility, it is strongly suggested that you have a valid database backup before running it **
If this "delete operation" is a regular feature of the application resulting in full table content deletion: the contents of a complete table are deleted at the end of every business day. It is recommended to consider moving this table and it's associated indexes to a separate Storage Area and then to run the "proutil dbname -C truncate area" as an alternate method..