Navigation:  Advantage SQL > SQL Functionality > Indexes with Expressions Advantage SQL Engine

While the Advantage API allows expressions in indexes, SQL syntax does not. For example, the SQL statement "create index descript on bugbase (bug_name)" is a legal statement, while "create index descript on bugbase (lcase(bug_name))" is an illegal statement. If an expression index is necessary, use the direct Advantage Client Engine function AdsCreateIndex() or sp_CreateIndex() to create an expression index.

In a subsequent SQL query, the Advantage SQL expression engine will convert certain SQL scalar functions in the SQL command onto the equivalent Advantage expression functions, so then the Advantage expression index can be used if the expressions match. By carefully choosing an Advantage index expression, SQL query performance could greatly benefit.

Given a common query such as "lcase(lastname) = 'smith'" (to find all occurances of 'Smith' regardless of case), an index might provide better performance. For example, use AdsCreateIndex() or sp_CreateIndex() to create an index on lower(lastname). The SQL query "select * from payroll where lcase(lastname) = 'smith'" will use that expression index.

Using the SQL statement "select * from payroll where lcase(lastname) = 'smith' and lcase(firstname) = 'john'" may or may not be optimized. If there is no index available, a non-optimized AOF is used and traditional, but slower, filtering of each record will be done to optimize the AOF. If the expression index lower(lastname) exists, then the query is partially optimized. For partial optimization, the AOF is used to reduce the result set that must be subsequently filtered with a traditional record filter. If two expression indexes (one on lower(lastname) and one on lower(firstname)) exist, then the query will be fully optimized, and AOFs can be used for all filtering.

Also note that the ability to map the SQL expression to an index expression is a controlling factor in whether the result of a SELECT statement will be a live or static cursor. If an expression used in a WHERE clause of a SELECT statement cannot be mapped to an index expression, then the result of the query will be a static cursor.

The following table lists the SQL scalar functions mapped to Advantage scalar functions. By creating an index using any of the following Advantage scalar functions or expressions, an SQL query using the mapped scalar function will benefit.

 

 

Using any of the following SQL scalar functions disqualifies the expression from using an existing index.

Math

 

String

 

Date/Time

 

Misc