Overview of Index Files

An index file is one that contains one or more index orders, also referred to as tags. An index file that contains only one index order is called a single-order index, but these are rare. Because you often need to access a table in a number of different ways, most developers include many index orders in a given index file. An index file that has two or more index orders is referred to as a multiple-order index file, or compound index file. A given index file can contain up to 50 index orders.

Not all index files are alike. Index files that have the same name as their associated table are known as structural indexes. The primary advantage of structural index files is that they are auto-open index files. Specifically, if you open a table, the Advantage Database Server (ADS) automatically opens that table’s structural index. Likewise, when you close the table, the structural index is closed for you.

A given table can have up to 15 index files. However, only the structural index file is an auto-open index file. Any additional index files associated with a given table must be explicitly opened and closed through code (or the appropriate utility in the Advantage Data Architect). If you make any changes to a table without opening all of its indexes, those indexes that were left closed would likely be logically corrupt, in which case they would need to be rebuilt.

Note

When a table is associated with a data dictionary, and the table is being opened using the data dictionary, all of the table’s indexes are opened automatically.

Index orders consist of zero or more keys. A key has two parts, an index key expression and a reference to a record in a table. The index key expression is used to sort and search the keys in the index. Most index key expressions consist of data from one or more fields in a table. For example, an index key expression may consist of the data from the customer ID field of the customer table. The associated index could then be used to order customers by their customer ID, as well as to search for records based on customer ID. But index key expressions can also be more involved, and may include two or more field values, constants, functions, and a variety of operators, including concatenation, arithmetic, and Boolean.

Every record in an ADS table has a unique record number, and each key points to a single record using this record number. Some types of index orders, such as expression indexes, have one key in the index order for each record in the associated table. Other index orders, such as conditional index orders and custom index orders, may have far fewer keys than records in the associated table. As a result, these index orders refer only to some of a table’s records. Finally, an FTS index may have many keys for each record, with one key for each indexed word in each record.

Why Create Indexes?

There are two reasons for creating an index order. The first is to provide a meaningful view of the records in a table. The views may be related to the order of a table’s records, or they may be related to the specific contents of individual records. The second reason is related to database performance.

The physical order of records in a table, sometimes called the natural order, is rarely very useful. The natural order is a consequence of the order in which the records were added in conjunction with records that were deleted. For example, when using ADT tables, a newly added record may be placed in a position where a previously deleted record appeared.

Using index orders, you can provide many different and meaningful orderings for your records. For example, there may be times when you want to access your customer table in order of the customer ID number. At other times, you may want to work with your customer records sorted by the customer’s last name. Both of these orders are possible by creating one index order that organizes records by customer ID, and another that orders records by customer last name. Which sort order the customer table appears in depends on which of these two indexes is currently active.

Index orders that are based on the contents of individual records can be used to work with a subset of records from a table. For example, you may have a table that contains records for employees, both past and present. One of the fields in this table may indicate the status of the employee, whether the employee is a current employee or a former employee. If you regularly want to work only with current employees, you can create an index order that ignores former employees. When that index order is active, the table will appear to contain only records for current employees.

As noted earlier in this section, the second reason to create an index order is to enable high-performance operations on your data. Imagine, for instance, that you need to work with a record where the customer ID number is 10304. If you have an index order whose index key expression is based on the customer ID field, ADS will locate the record using this index order. Specifically, it will locate the record by first searching for the customer ID in the keys of this index order, and then use the key’s pointer to the record to retrieve the data. In other words, the underlying table is not even touched until the server knows which record it needs to read. This approach is much faster than if the server had to read every record in the table looking for the particular customer ID.

While the preceding example considered the location of a single record, these indexes also allow groups of records to be quickly identified. For example, if you have an index key expression based on the customer table’s Last Name field, you can quickly locate all customers whose last name begins with a given letter, such as W. Likewise, if you have an index on the City Name field, you can very quickly obtain a list of customers who reside in a particular city—New York City, for example. In both of these cases, ADS can quickly find the appropriate customer records by reading the keys of the associated index orders, which is always faster than reading the individual customer records.

It is this second characteristic of index order, the identification of record locations using the indexes directly, that is the source of ADS’s blinding performance.

Here’s another way to look at it. If you design your indexes correctly, ADS will provide you with the best performance possible. On the other hand, if you fail to define indexes based on how your application needs to work with its data, your application’s performance will suffer. For example, if you do not have an index order whose index key expression is based on the City Name field, searching for customers who live in New York City will be significantly slower.

Types of Index Orders

ADT tables support four types of index orders. These are expression indexes, conditional indexes, custom indexes, and FTS indexes. Each of these types is described in the following sections.

Expression Indexes

An expression index is a simple index order that consists of one key for each record. As is the case with all ADS index orders, with the exception of FTS indexes, the value of each key is defined by an index key expression.

A common type of index key expression is one that is based on one or more fields of a table. For example, assuming that there is a table that includes a field named Country, the following index key expression builds an index order based on this field:

Country

When building an index key expression on two or more fields of an ADT table, you separate the field names with a semicolon. For example, the following index key expression defines an index based on the Invoice Number and Part Number fields:

Invoice Number;Part Number

For DBF index files, this same expression index would be represented by something like the following:

INVNUM + PARTNO

Note

For the preceding DBF index order to be meaningful, both INVNUM and PARTNO would need to be string fields. If they were numeric fields, it would be necessary to the expression engine STR function to convert them to a string before concatenating the two values. The resulting expression would look like STR(INVNUM) + STR(PARTNO). If the fields were not converted to a string, the resulting expression would be the sum of the INVNUM and PARTNO fields, which would be worthless. This conversion is not necessary for ADT tables when a semicolon is used to concatenate fields. With ADT index order expressions, a semicolon can concatenate expressions of differing types. For example, a string field can be concatenated with an integer field.

While expression indexes based on one or more fields are the most common, these indexes can also include more complicated expressions. For example, they can include two or more expressions, as well as basic arithmetic, string, and Boolean operators. These operators include the following:

+	-	*	/
AND	OR	NOT

In addition, expressions can use a variety of functions supported by the ADS expression engine. The following is the list of these functions as of this writing:

ABS	ALLTRIM	AT	CHR	CTOD	CTOT
CTOTS	DATE	DAY	DELETED	DESCEND	DTOC
DTOS	EMPTY	I2BIN	IIF	L2BIN	LEFT
LEN	LOWER	LOWERW	LTRIM	MAX	MIN
MONTH	NOW	PAD	PADC	PADL	PADR
RAT	RECNO	REVERSE	RIGHT	ROUND	RTRIM
SPACE	STOD	STOTS	STR	STRZERO	SUBSTR
TIME	TRIM	TODAY	TRANSFORM	TRIM	UPPER
UPPERW	VAL	YEAR

Consider this: expression indexes are case sensitive. To create an expression index that can be used to sort and search a text field without regard to case, you can use the UPPER function (or LOWER function). The following expression defines an index that can be used to create a case-insensitive filter on a field named Last Name:

UPPER(Last Name)

It is important to note, however, that with an index like this, your filter expression or SQL SELECT WHERE clause must also use the UPPER function. Otherwise, the selection operation will not be optimized. More is said about this later in this chapter.

In addition to their other features, expression indexes can be descending indexes and they can be unique. When you make a descending expression index active, the records in your table are sorted in descending order.

What a unique expression index does depends on whether the associated table is an ADT table or a DBF table. With DBF tables, a unique index contains one key for each distinct value on the index expression, but the index does not enforce uniqueness among records. For example, if you define a unique index on a DBF table field named Customer ID, it is still possible for this table to contain two different records that had the same value in the Customer ID field. However, when this index is active, only one of those records would be accessible. (This behavior is similar to a SQL SELECT statement that uses the DISTINCT keyword.)

By comparison, a unique index on an ADT table enforces uniqueness. For example, if you have a unique index on the Customer ID field of an ADT table, an attempt to write another record to this table using the same customer ID value as one that already exists in the table will fail.

Expression indexes are the workhorse indexes of your database applications. In fact, conditional, subindex, and custom index orders are really just special cases of expression indexes. In short, they provide you with ordered, high-speed access to your table’s records based on index key expressions.

Conditional Indexes

A conditional index is similar to an expression index. The major difference is that while an expression index has a key for every record in the underlying table, a conditional index may not.

There are two parts to a conditional index: the index key expression and the condition expression. The index key expression serves the same purpose as in an expression index. Specifically, it defines the contents of the index key, which is used both for sorting and for high-speed access to the underlying records.

The second part is the condition expression. The condition expression is a Boolean expression, and it typically compares values in one or more fields to one or more expressions. A conditional index has one key for each record where the condition evaluates to a Boolean True.

When a conditional index is made the current index of a table, only those records for which there are keys appear visible in the table. Those records appear in the order defined by the index key expression. For example, if you want to create an index that includes only customers from the USA, and you want those records to be sorted by the customer ID, you define the index key expression as

Customer ID

and the condition expression as

Country='USA'

At first glance, conditional indexes sound appealing. But in practice, there are more flexible mechanisms for achieving the same result. For example, you can use a SQL SELECT statement to select a subset of records as well as to sort the records that are displayed. Since SQL SELECT queries can contain parameters in their WHERE clauses, the Boolean expression that selects the subset of records can be controlled at runtime. By comparison, the Boolean expression employed by a conditional index is fixed when the index is created.

An even more flexible solution is to use an expression index in conjunction with a scope (sometimes called a range) or a filter. Like a conditional index, a scope on an expression index can limit which records appear in the table, as well as provide a sorted view of the records that do appear. Filters provide a similar feature, and while slightly slower than scopes, enable more flexible expressions than scopes. Specifically, a filter expression can include more fields than are contained in the index key expression. Importantly, both scopes and filters can be modified at runtime. Furthermore, unlike SQL SELECT statements, expression indexes provide for index-based, high-speed access to a table’s records.

This discussion is not meant to suggest that you should avoid conditional indexes. Indeed, they are high-performance indexes with many valuable characteristics. However, there are alternatives. Which indexes and filtering mechanism are best for your application depends on the nature of your data and how you need to access it.

Custom Indexes

A custom index is an initially empty index whose keys are added at runtime. For example, you can have a custom index named ShowCustomers. You can then add keys for each customer that you want displayed in the table when the index is applied. As is the case with the other indexes discussed in this section, the keys of a custom index are defined using an index key expression.

You add a custom key using the ACE (Advantage Client Engine) API by calling AdsAddCustomKey. This method adds a key to the custom index that references the current record of a table. Similarly, AdsDeleteCustomKey removes a key for the current record from the custom index. Some of the other Advantage data access mechanisms provide additional functions for adding and removing keys from a custom index.

While a custom index might sound like a useful tool, you need to be careful when using a custom index in a multiuser environment. Specifically, all client applications that are using the same custom index can add or remove keys from that index, so long as the index is opened in a shared mode. Also, keys that are added to a custom index are based on the data in the associated record at the time that the key was added.

FTS Indexes

FTS indexes enable high-speed searches of text-based fields in ADT and FoxPro DBF tables. (Clipper NTX indexes do not support FTS indexes.) Unlike the other indexes introduced in this section, the keys of an FTS are not based on an index key expression. Instead, they are based on the words of the text-based field on which they are built.

Each FTS index applies to only a single text-based field. You can create an FTS index on any of the following field types: BINARY, CHARACTER, MEMO, RAW (ADT tables only), and IMAGE.

FTS indexes are used by filter expressions and the WHERE clause of SQL SELECT statements to select records based on their text content. These filter expressions must use the CONTAINS scalar function, and WHERE clauses must contain one or more of the following scalar functions: CONTAINS, SCORE, and SCOREDISTINCT.

Note

See the section “Full Text Search Indexes” later in this chapter for in-depth coverage of these indexes.