Kbase P27066: What is the difference between idxfix idxcheck and idxbuild?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/08/2009 |
|
Status: Verified
GOAL:
What is the difference between idxfix idxcheck and idxbuild?
GOAL:
What are the differences between the index utilities: idxfix idxcheck and idxbuild?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
FIX:
Although idxcheck, idxfix and idxbuild are all Progress utilities that address indexes, they are very different routines which use different algorithms and logic.
The idxbuild utility must be run offline. Idxbuild's functionality is, if you will, the 'brute force' or ?black box? utility for rebuilding indexes. It is an offline utility that literally 'rebuilds? indexes from scratch then re-activates them. During the execution of idxbuild, at no time are the existing indexes ever checked but, any errors encountered during the index building process will be reported. Some errors are fatal, and if these occur the database cannot be recovered. For this reason a backup of the database /must/ be performed before running idxbuild. When the process is complete, the rebuilt indexes will replace old indexes. In other words, if there were index corruption in old indexes, they would not be detected, because the utility replaces them with the newly rebuilt indexes. There is a fair amount of sorting that ensues. The speed sort parameters -TB and -TM should be considered together with -B for buffer and available disc space to accommodate the size that the .srt file may reach. Approximately 80% of the database size will be needed for sorting, more if word indexes are involved. If the database exceeds 2GB in size, then a multi-volume sort file must be defined beforehand. (Please refer to P55740 for more information).
The idxcheck utility must be run offline in versions pre-OpenEdge 10.1B. The functionally, as the name implies, is the checking of indexes and does not alter them in any way, even if it finds errors. So if an idxcheck operation is interrupted or crashes, it will not cause data corruption.
Idxcheck works by:
1.) Checking that all index entries associated with each record can be found
2.) Verifying that each value in the index is in the associated record
3.) Undertaking a validation of all index blocks
and reporting an associated error as appropriate. It was designed to detect index corruption as sensitively as possible, there are lots of reads taking place during the process which, depending on the number of records and associated indexes, may take some time to complete. The idxcheck does not perform crash recovery. It does not access the BI. (Please refer to P27013 for more information).
The idxfix utility can be run online or offline. Option 3 of idxfix is very much like idxcheck, except that:
It could possibly also detect orphan index entries as well as orphan records while idxcheck could not detect orphan index entries and Idxbuild will not recycle orphan index blocks for schema indexes. When run in ?report and fix? mode it will repair faulty indexes on the fly.
The major advantage over the other two utilities is that when it is run online, it can be run multi-threaded. However, when idxfix is run online, it is run SHARE-LOCK, so there may be contention for records that slows the process down. Ending the operation online when run in ?report and fix mode?, should not cause corruption because changes performed by idxfix are logged in the before-image file (and in the after-image files, if after-imaging is enabled), so normal crash recovery will take place and any incomplete index changes will be undone. Unlike an idxbuild operation which MUST be run to completion, it will merely wait to backout the last operation. (Please refer to P23045 for more information).
Only idxbuild can fix actual corruption within the index block, by rebuilding the entire index and creating new index entries within the index blocks. Physical corruption can be revealed when performing record operations like finding, updating, creating and deleting database records, where traversing the index tree to update, create or delete index entries encounters a problem.
Idxfix c.an repair relational integrity problems, such as adding missing index entries to an index for an existing database table record, and deleting existing index entries that do not have a corresponding database table record..