Integration: Relational Databases

Updating Relational Databases

Omnidex is most commonly implemented on read-only tables. Many Omnidex applications take advantage of tens to hundreds of indexes per table, and online updates are impractical in this situation. Omnidex applications are usually built around tables that are refreshed on a daily, weekly or monthly basis. Once the data has been updated in the relational database, the indexes are refreshed using the UPDATE INDEXES statement. Omnidex can quickly build all of the indexes at once, allowing the freshly indexed application to be deployed.

Omnidex also supports real-time updates using the SQL INSERT, DELETE and UPDATE statements. These statements will update both the underlying relational table and the Omnidex indexes. This approach is appropriate for tables with a low volume of updates, or for tables with minimal indexing.

Omnidex's INSERT, DELETE and UPDATE statements do not support transaction management or isolation levels. Each INSERT, DELETE or UPDATE is considered a transaction unto itself. Both data and indexes are visible to all users as soon as they are made.

For Oracle and SQL Server databases, Omnidex allows the indexes to be updated by relational database triggers using Automatic Index Maintenance. Triggers are installed on each table that posts update transactions to a common log file. A separate process reads committed transactions from this table and posts them to the Omnidex indexes. This approach allows Omnidex to capture committed updates from all sources without changing those aspects of the application. At the same time, it does introduce a delay between the change to the data and a change to the indexes. This delay can vary depending on the volume of updates and the number of indexes.

Additional Resources

See also:

Back to top
integration/rdbms/updates.txt ยท Last modified: 2016/06/28 22:38 (external edit)