Advantage Database Server

Indexes with Expressions

Advantage SQL Engine

Indexes with Expressions

Advantage SQL Engine

Previous topic Next topic  

Indexes with Expressions

Advantage SQL Engine

Previous topic Next topic  

While the Advantage API allows expressions in indexes, SQL 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() 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.

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, an SQL query using the mapped scalar function will benefit.

 

SQL Scalar

Advantage Function

ABS

ABS

BIT_LENGTH

LEN*8

CHAR

CHR

CHAR_LENGTH

LEN

CHARACTER_LENGTH

LEN

CONCAT

string '+'

CONTAINS

CONTAINS

CURDATE

DATE

CURRENT_DATE

DATE

CURRENT_TIME

CTOT( TIME() )

CURTIME

CTOT( TIME() )

DAYNAME

DAYNAME

DAYOFWEEK

DAYOFWEEK

DAYOFYEAR

DAYOFYEAR

EMPTY

EMPTY

HOUR

HOUR

IFNULL( expr, val )

ADT and VFP:

IIF( ISNULL( expr ), val )

 

CDX and NTX:

IIF( EMPTY( expr ), val )

 

ISNULL( expr, val )

ADT and VFP:

IIF( ISNULL( expr ), val )

 

CDX and NTX:

IIF( EMPTY( expr ), val )

 

IIF

IIF

ISOWEEK

ISOWEEK

LCASE

LOWER

LEFT

LEFT

LENGTH

LEN(RTRIM())

LOCATE(x,y)

AT

LOWER

LOWER

LTRIM

LTRIM

MINUTE

MINUTE

MONTHNAME

MONTHNAME

NOW

NOW

OCTET_LENGTH

LEN

PI

3.1415926535897932

POSITION

AT

POWER

**

QUARTER

QUARTER

RIGHT

RIGHT

RTRIM

RTRIM

SECOND

SECOND

SPACE

SPACE

SUBSTRING

SUBSTR

UCASE

UPPER

UPPER

UPPER

WEEK

WEEK

 

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

Math

ACOS

LOG

ASIN

LOG10

ATAN

MOD

ATAN2

RADIANS

CEILING

RAND

COS

ROUND

COT

SIGN

DEGREES

SIN

EXP

SQRT

FLOOR

TAN

 

String

ASCII

REPLACE

INSERT

TRUNCATE

REPEAT

 

 

Date/Time

CURRENT_TIMESTAMP

TIMESTAMPADD

CURRENT_TIMESTAMP_UTC

TIMESTAMPDIFF

DAYOFMONTH

 

 

Misc

CAST

IFNULL

CONVERT

ISNULL

DATABASE

SOUNDEX

DIFFERENCE

USER

EXTRACT