Most Omnidex environment by replicating all of the tables and columns in the underlying database. With relational databases, a partial subset of tables and columns can be declared, allowing for a smaller database that is easier to manage. At times, however, administrators may want to add additional columns that do not exist in the underlying database. These columns are usually derived from other columns in a table, such as arithmetic expressions, string expressions or logic expressions.
Omnidex allows administrators to create expression-based columns. An expression-based column is simply a column at the end of a table declaration that is fulfilled by a SQL expression rather than an actual column in the underlying database. The SQL expressions can use standard SQL language, and can also use Omnidex extensions while accessing all of the other columns in the table. Each time a row is accessed, the expression is calculated and the result is deposited into the column.
Expression-based columns can be referenced in SQL statements just like any other column in the table. Expression-based columns can also be indexed, allowing for many options for optimizing queries. For example, if an expression-based column called TOTAL multiples AMOUNT * QUANTITY, then Omnidex will watch for SQL statements that search for “AMOUNT * QUANTITY > x”. When Omnidex finds a match between a SQL expression and an indexed expression-based column, it will automatically use the index to optimize the query.
Expression-based indexes are a powerful feature than can greatly extend the capabilities of a database. It is also a convenient method of expanding on an existing database without having to make changes to the underlying data files or relational database.
See also: