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.
Standard Omnidex indexes are appropriate for optimizing criteria where a column is being compared to one or more values. For example, standard Omnidex indexes are excellent for optimizing the criteria, “STATE = 'MA' and CITY = 'Boston'”. By default, Omnidex indexes cannot be used to optimize criteria where an expression is being compared to one or more values. For example, standard Omnidex indexes cannot be used to optimize the criteria, “((QUANTITY * UNIT_COST) * TAX_RATE) + COMMISSION) > 100.00”.
In these situations, Omnidex will look for an expression-based column that matches the expression in the SQL statement. If one is found, Omnidex will look to see if that expression-based column has been indexed. If an index is found, Omnidex will optimize the query by using that index. This has two advantages. First, it gains the speed of an indexed search, and it prevents the expression from be recalculated for each row as it is retrieved.
This same technique is appropriate for many types of expressions. For example, a search for area code using criteria of “SUBSTRING(PHONE from 2 for 3) = '303'” can be optimized using a similarly constructed expression-based column. A search for age using criteria of “$COMPARE_DATES(BIRTHDATE, CURRENT_DATE, 'YY') >= 24” can be similarly optimized. This technique creates a wide variety of new oppportunities for optimizing queries using Omnidex indexes.
See also: