|Powered by QM on a Rpi server|
KnowledgeBase 00081: Creating Alternate Key Indices in Live Systems
This article was originally published as a Tip of the Week.
An alternate key index is created using the the CREATE.INDEX command and then populated using BUILD.INDEX. QM also provides a MAKE.INDEX command that combines the two stages.
It is always safe to set up a new index on a file where a developer believes that it may be beneficial. In the very unlikely worst case, the cost of maintaining the index might be greater than the performance gained by use of the index. If this is found to be true, it is always safe to delete an index so long as the application does not use operations such as SELECTINDEX that directly access the index data.
QM builds an in-memory list of the indices that exist on a file when the file is opened. This list will be examined on every file update operation and on parsing query processor selection clauses.
In some other multivalue products, creating a new index while other users have the file open will cause the newly created index to become incorrect as soon as an update is made by another process because it is unaware of the new index. This does not happen in QM as index creation or deletion notifies all other QM processes that the in-memory index list must be reloaded. This mechanism enables indices to be added or removed from live 24x7 systems where there is no opportunity to take the system offline during index construction.
The CONCURRENT Keyword
By default, the build phase of index creation during which the data file is scanned to populate the index takes an exclusive file update lock, suspending updates to the file until the build is complete. Use of the CONCURRENT keyword to BUILD.INDEX or MAKE.INDEX performs the build in a manner that allows other users to continue to update the file while the build is in progress though there will be some degradation of performance during the build.