Integration: Relational Databases

SQL Server

Updates

Omnidex is primarily used on read-only databases; however, Omnidex can also be used on read-write databases. Omnidex supports the INSERT, DELETE and UPDATE SQL statements. There are several restrictions that administrators must be aware of before deciding whether to use Omnidex on a read-write database:

Omnidex does not support database transactions.
Omnidex does not support the relational ACID requirements (Atomicity, Consistency, Isolation and Durability).
Most Omnidex applications will place tens or hundreds of indexes on a table, and this makes online updates impractical.
Omnidex Bitmap indexes are not updated on INSERT, DELETE or UPDATE statements. Omnidex Bitmap indexes are used to improve queries that reference low-cardinality data, and are heavily used on tables containing millions of rows.
Omnidex Grids do not support INSERT, DELETE or UPDATE statements.
Omnidex Rollup Tables are not updated on INSERT, DELETE or UPDATE statements.
Omnidex Text Dictionaries are not updated on INSERT, DELETE or UPDATE statements.
Omnidex cannot use Cached Qualification Optimizations on tables when tables are updated using INSERT, DELETE or UPDATE statements.
Omnidex UPDATE statements do not allow SET commands to values other than literals or constants.

Given these restrictions, most Omnidex applications refresh the data at regular intervals such as daily, weekly, or monthly. Once the data is refreshed, the indexes are rebuilt, producing an indexed version of the new data. This data can then be deployed into production.

For applications that require online updates, applications can use INSERT, DELETE, and UPDATE statements to update the table. This approach immediately updates both the database and the Omnidex indexes. Alternatively, Omnidex allows SQL Server triggers to be installed on a table that copies changes to a separate transaction table. The OdxAIM program can generate SQL Server triggers for tables with Omnidex indexes. These SQL Server triggers log the changes to the data in a separate, intermediate transaction table, and then OdxAIM processes this transaction table in the background to update the Omnidex indexes. This approach preserves transactional integrity, though there is a short delay between the updates of the database and the indexes.

Prev | Next

Integration: Relational Databases

SQL Server

Updates

Additional Resources