Top  Previous  Next


The MAKE.INDEX command creates and builds an alternate key index. It is equivalent to use of CREATE.INDEX followed by BUILD.INDEX.





MAKE.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 MAKE.INDEX command creates the file structures to hold an alternate key index and then builds the index.


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 NO.NULLS 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.


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 MAKE.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 program.


Except when used with the CONCURRENT keyword, the MAKE.INDEX command requires exclusive access to the file and may take some time to complete for a very large file. It is therefore best executed at quiet times. The CONCURRENT keyword allows an index to be built while the file is in use. There is a performance overhead for this and the QMBasic SELECTINDEX, SELECTLEFT and SELECTRIGHT statements may stall waiting for the build to complete.




Data Encryption


Alternate key indices may be applied to files that use record level data encryption but developers should be aware that the index itself is not encrypted and hence weakens the security of the indexed fields.


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 command creates and builds an index on the DATE field of the ORDERS file.


See also: