Top  Previous  Next


The CREATE.INDEX command creates an alternate key index.





CREATE.INDEX filename field(s)  {options}




filenameis 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.


optionsare any combination of the following:





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 record. 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.


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.








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


See also: