Advantage Developer Zone

 
 
 

Optimizing Index Page Sizes

Saturday, August 14, 2004

The maximum index key size in Advantage 7.1 is 4082 bytes for ADI index files, whereas prior versions had a limit of 240 bytes. This means it is now possible to create ADI indexes on much larger fields as well as many concatenated fields. This also means there are several possibilities for increasing index performance by optimizing the use of index pages sizes based upon the index key size. The following table shows what has been calculated as the optimal index file page size given an index key size. Therefore, for optimal performance of indexes, it may make sense to put your indexes into multiple index files based upon index key size, and to create those index files with the optimal index page size as shown. For additional details on index key sizes and index page sizes, read the additional information in this tech tip provided after this table.

Key Size (in bytes)

Index File Page Size

1 - 158

512

159 - 329

1024

330 - 670

2048

671 - 1353

4096

1354 - 4082

8192

The key size for character fields is equal to the number of characters. So an index on a 50-byte character field will have 50 byte keys. For an index composed of concatenated fields, the key size is simply the sum of the individual field key sizes. The key size for all numeric (integer, double, money, currency, etc.), date, and timestamp field types is 8 bytes. Keys for time fields are 4 bytes, and keys for logical fields are 1 byte. Keys for raw fields are 1 byte for each byte in the field. The index management utility in Advantage Data Architect can be used to view the key size for any index if you are in doubt about its length.

There are relationships between the key and index file page size that you should be aware of. For a given page size, there is a maximum key size that is supported and there is a maximum key size that should be used in order to guarantee optimal balancing of the index. This means that if you add a new index to an existing file, it is possible to violate one of these two relationships. If the key size is too large for the index file page size, you will get error code 5188 on the attempt.

When you create a new index in a new index file without specifying the page size, Advantage will compute the page size to match the values in the above table based on the key size. Once an index file is created, the page size is fixed. You can change the page size on an existing file by re-indexing it. Advantage does not compute optimal page sizes when re-indexing; it uses the existing page size unless a new size is specified.

If you are going to be using larger key sizes and want to keep all indexes in a single index file, then you should use one of two approaches. The first is to make sure that the first index created in a new index file is the one with the largest key. Advantage will compute the optimal page size, and the next indexes with smaller key sizes will be sure to fit.

The second approach is to specify the page size when creating new index files. When using SQL, for example, to create indexes, you can specify the page size to use. The following SQL script creates a table with two indexes in the same index file. The PAGESIZE option is only applicable when creating a brand new index file. It is ignored if creating an index in an existing index file.

create table test (shortfield char(25), longfield char(500));
create index isf on test (shortfield) PAGESIZE 2048;
create index lsf on test (longfield);

Another approach that can be used if you have existing indexes is to create indexes that require larger page sizes in separate index files. If you are using an Advantage Data Dictionary, this approach presents no additional work to the developer. All index files associated with a table in a data dictionary are opened automatically when the table is opened. If you are not using a data dictionary, then only the structural index file (the index file with the same base name as the table) is opened automatically. The following SQL script shows how to create indexes in separate index files:

create table test (shortfield char