Updating the Indexes

With Omnidex indexing, many options are available for updating indexes as data changes.

When indexing needs to be performed immediately after a record is changed, real-time indexing is required and can be implemented in both synchronous and asynchronous fashions. Synchronous indexing is the easiest to implement, while asynchronous indexing maximizes throughput of transactions. The choice of options depends on how many keys are involved, how much activity must be indexed and the complexity of implementing one over the other.

Real-Time Indexing

Batch Indexing

Deferred Indexing with ODXAIM

Indexing in Real-Time

When indexing volatile online databases, it is usually desirable to index in real-time. Update activity is not as intense as during a batch update, but response time to the user must be reasonable to maximize efficiency of the data entry personnel. Consequently, the options for maintaining indexes depend on the level of activity and the number of keys being indexed on the affected tables.

For online activity with a relatively small number of updates, real-time, synchronous indexing is the preferred option. This is true even where many keys are involved. The slight hesitation involved with indexing a number of columns is a small price to pay for the time that is saved every time the record is searched on and retrieved in the future.

As the number of transactions or the number of keys increases to a point where response time is adversely affected, real-time, asynchronous indexing using ODXAIM, may be a better solution. This allows data updates to be completed without regard to the index updates, which are completed in the background.

If asynchronous updating is not desired for some reason, then indexing in batch is always an option. However, batch updates require exclusive access to the Omnidex indexes which precludes users from updating Omnidex-indexed data during the index build process.

Indexing in Batch

Databases that are very static, such as data warehouses, data marts and other archived data structures are typically updated in periodic batches rather than through online data entry. As such, the methods best suited for indexing these changes are typically batch-oriented, and depend on the overall database size, the frequency of updates and the relative number of records.

If the frequency of updating is rare, say quarterly or yearly, then the choice is usually to re-index after the batch updates. Even in large databases, indexing can be completed at very high rates of speed with Omnidex, freeing the database for online access for the rest of the quarter or year.

If the database is updated more frequently, say weekly or nightly, then database size and the volume of updates play a part in the decision. Again, if the database can be indexed in a night or a weekend, and the batch window allows for the database to be unavailable for that time, then indexing in batch is usually the best (fastest and easiest) choice.

Deferred Indexing with ODXAIM

ODXAIM, Omnidex Automatic Index Maintenance, automatically maintains index synchronization for applications that do not use OmniAccess, using database triggers to signal when an update has occurred. It also enables transaction management on the Omnidex indexes.

• Applications like SQL Plus work directly with the underlying database management system and are unaware of Omnidex or the Omnidex indexes. Normally, when data is updated through SQL Plus, the Omnidex indexes remain unchanged. Through database triggers, ODXAIM automatically updates the indexes with these changes.
• If transaction management is set up on a table installed with Omnidex indexes, ODXAIM causes those indexes to inherit the transaction management. When a change is made to the table, a record is written to a table named ODXTRANS. ODXAIM reads the ODXTRANS table and updates the indexes accordingly. If the original transaction fails, the record is never written to ODXTRANS.

With ODXAIM, indexes are not maintained in real-time. However, the delay is minimal, a few minutes or less, and the performance gain to the update application can be considerable.

Top