CREATE.INDEX

The CREATE.INDEX command creates an alternate key index.

Format

CREATE.INDEX filename field(s) {options}

where

filename

is the name of the file for which the index is to be built.

field(s)

is one or more field names for which indices are to be created.

options

are any combination of the following:

DELETING

ENCRYPT

EXCLUDE "value"

NO.CASE

NO.NULLS

PATHNAME index.path

The CREATE.INDEX command creates the file structures to hold an alternate key index. The index must subsequently be populated using BUILD.INDEX before it can be used.

The field(s) referenced in the command must correspond to C, D, E, I, A or S-type dictionary items. The dictionary items can be deleted once the index has been constructed as all details of the indexed field are stored in the index file but this is not recommended. The value to be indexed must not exceed 255 characters. Values longer than this will not be included in the index.

Indices constructed on I or C-type dictionary items or on A or S-type items that use correlative expressions should be such that they always produce the same result when executed for the same data. Examples of possibly invalid I-type expressions would be those that use the date or time and those that use the TRANS() function to access other files.

The DELETING option causes CREATE.INDEX to delete any previous version of the same index. It is useful when, for example, the index must be recreated because an I-type expression that derives the indexed value has been modified.

The NO.NULLS option specifies that no entry is to be added to the index for records where the indexed field is null.

The NO.CASE option specifies that the index is to be built using case insensitive key values. The internal sort order of the index is based on the uppercase form of the data being indexed. A case insensitive index can be used with case insensitive comparison operators in the query processor but not with case sensitive operations because of the sort order difference. Conversely, a case sensitive index can be used with case sensitive comparison operators in the query processor but not with case insensitive operations.

The ENCRYPT option creates an encrypted index. This is only possible when the data file being indexed uses record level encryption. There is a significant performance penalty in using encrypted indices with files that are frequently updated.

The EXCLUDE option causes QM to omit index entries for the specified value. Multiple values can be excluded by use of a comma separate list such as

CREATE.INDEX SALES EXCLUDE "D", "X"

See the discussion of index exclusions in the alternate key index overview for more detail of this feature.

Normally, the indices are stored as subfiles in the directory that represents the data file. The PATHNAME option allows the indices to be stored in an alternative location. This might be useful, for example, to balance loads across multiple disks or to exclude indices from backups as they can always be recreated.

All indices for a single data file must be stored together. The PATHNAME option can be used when creating the first index and specifies the pathname of a new directory that will be created at the same time as the index. If this option is included when creating subsequent indices the index.path must be the same as for the first index. It is suggested that the pathname should be based on the data file name for ease of recognition.

If the PATHNAME option is not included, the CREATE.INDEX command looks for an X-type VOC record named $INDEX.PATH in which the second field contains the pathname of a directory under which indices are to be created by default. If found, a subdirectory with the same name as the final element of the data file pathname is created under this location. For example, when creating an index for a file with pathname /hd1/sales/invoices, a $INDEX.PATH record that contains

1: X

2: /hd2/indices

would place the indices in /hd2/indices/invoices.

Use of PATHNAME DEFAULT will ignore the $INDEX.PATH record, placing the indices in the same directory as the data file elements.

Index subfiles can be moved using the operating system level qmidx utility.

Data Encryption

Alternate key indices may be applied to files that use record level data encryption and the index itself can also be encrypted as described above. Files using field level encryption cannot have indices on encrypted fields. Also, indices constructed from calculated values such as I-types that use encrypted fields will fail if the record is updated by a user that does not have access to the relevant encryption key.

Example

CREATE.INDEX ORDERS DATE

BUILD.INDEX ORDERS DATE

The above commands create and build an index on the DATE field of the ORDERS file.