Advantage Developer Zone


Enhanced multi-segment index support with 7.1

Tuesday, December 14, 2004

Prior to 7.1, developers often needed to create single-field indexes to ensure that SQL statements and filters were fully optimized. With 7.1, the Advantage Optimized Filter engine now uses multi-segment (composite) indexes when optimizing expressions involving multiple comparisons combined with AND operators. For example, if a composite index of the form lastname;firstname (or lastname+firstname with DBF tables) exists, then filters and SQL queries with expressions such as firstname=’John’ AND lastname=’Smith’ can be fully optimized using that index.

Basically, a filter or SQL statement will be optimized if it uses the first consecutive segments in an index. The segments can be used in any order. Consider the following filters with a firstname;middlename;lastname composite index:

middlename = ’Humphrey’ and firstname = ’John’ AND lastname = ’Smith’ Optimized: The filter includes the first consecutive segments. Order does not matter. Prior to 7.0, the filter would only be partially optimized.

lastname = ’Smith’ AND firstname = ’John’
Partially Optimized: The filter does not use the first consecutive segments. The filter is able to use the firstname segment for partial optimization. This is the same behavior as 7.0.

middlename = ’Humphrey’ AND lastname = ’Smith’
Non Optimized: The filter does not use the first consecutive segments. This is the same behavior as 7.0.

When using multiple segments of composite indexes for optimization, the Advantage expression engine can only optimize expressions using the = operator. See AOF Optimization in the Advantage Help file for more information.