logo
Powered by QM on a Rpi server
Home
About OpenQM
Sales and Downloads
Help and Support
About
Login

KnowledgeBase 00081: Creating Alternate Key Indices in Live Systems

Last updated: 17 Feb 2017
Applies to: All versions
Search  
Top level index       Full Index Search Tips
Previous article     Next article

This article was originally published as a Tip of the Week.

Index Creation

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.


Index Operation

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.


Related Articles

None.



Please tell us if this article was helpful
Very     Slightly     Not at all
Comments
Email (optional)