This is an old revision of the document!
Expression-based columns provide several opportunities for optimizing SQL statements. Expression-based columns can be used to classify data into more valuable formats. Expression-based columns are also the best way to index the result of expressions. In both of these cases, Omnidex will take advantage of indexes on expression-based columns to improve the performance of the query.
When developing applications, a common task is to classify data so that it is more easily presented to the user. For example, birthdates can be classified into age brackets. Income amounts can be classified into income levels. Credit scores can be classified to credit risk brackets. These tasks are often done during the “Extract, transform and load” (ETL) phase of an application, but it can also be done with expression-based columns.
These classifications help the user by giving them more categorized and simplify choices, and by standardizing searches. These classifications help indexing by enabling more use of the high-speed Omnidex Bitmap indexes. Omnidex Bitmap indexes are appropriate when a column has 32 or less unique values. While a birthdate does not mean this requirement, age brackets do. The same is true for income brackets, credit score ranges, and any number of other similar classifications.
Administrators may choose which approach they prefer. Classifying these at the ETL phase has the advantage of speeding eventual performance since the expressions are only calculated at the ETL phase, and not during indexing and database access. Classifying these with expression-based columns has the advantage of not having to change the underlying database and being able to add new classifications more quickly.
See also: