DEFINE-INDEX
Creates an index definition item in the dictionary of a data file to be indexed. This defines the name of the index, and the selection and sort criteria to be used to create it.
DEFINE-INDEX does not create the index, only the index definition item. The index name, which is the
Command class
English command.
Syntax
DEFINE-INDEX
TO: index-name
DEFINE-INDEX file-name{,data-section} index-name (R{O}
Syntax elements
file-name The name of the file in which the index definition will be created.
data-section The name of the data section with which the index will be associated. Must immediately follow file-name and be preceded by a comma. If omitted, the default data section is assumed.
selection-criteria Any selection criteria as defined in English Reference. If omitted, an index containing all items in the file is defined.
sort-criteria Any sort criteria as defined in English Reference. If omitted, an index sorted by item-id is defined.
USING file-specifier
Specifies that the file specified immediately after the USING connective is to be the source of definition items.
index-name The name of the index.
Option
L Specifies that right-aligned fields should treated as left-aligned; that is, as alphanumeric, rather than numeric. This ensures that any leading zeros are included in the keys.
N Excludes null entries from the index (by making it selective).
O Overwrites an existing index definition item.
R Recovers an index definition item from information contained in the specified index. The index definition information is extracted from the index and an appropriate index-definition item is built and written to the file dictionary. If an index-definition item with the same name already exists, this will not be overwritten unless the O option is also specified.
The R option can only be used if the index specified was created from an index definition item (see
U Specifies that the index should contain only unique keys; a write will fail if the key already exists for another item.
Prompt
Except when using the R option, DEFINE-INDEX prompts for the name of the index.
Comments
An index definition can only be deleted using the EDELETE command.
Caution
- If you create an index definition and then change a data definition item, the index definition is not changed. It still reflects the data definition at the time the index definition was created.
-
You should avoid referencing data definition items that return data that might change independently of the data in the indexed file, as this can produce an inconsistent index. Should an index become inconsistent, it might produce inaccurate query or selection results.
Examples of data that might give problems are:
- The current date and time.
- Data in a different file or data section, referenced through a data definition item using a file translation, where the data in the referenced file might change.
In the latter case, the problem can be overcome by deleting the index before making the changes and creating a new index afterwards. Note, however, that this is only essential if existing items are likely to change - the addition of new data items will not result in an inconsistent index, though the index will be out of date.
An index can be checked for validity by using the VERIFY-INDEX command.
The R option allows you to recover a corrupt index definition, or one that has been deleted in error. The recovered item will contain the compiled selection and sort criteria, but the English selection and sort criteria cannot be recovered. The recovered item will instead contain the text of the recovery command. Refer to the description of an Index Definition Item for more details.
See Indexing for more information on indexing.
Selective indexes
The DEFINE-INDEX command allows an index to be defined using BY clauses (BY, BY-DSND, BY-EXP, BY-EXP-DSND, BY-EXP-SUB and BY-EXP-SUB-DSND) to determine the index sort order and WITH clauses to restrict which items appear in the index. Indexes whose definitions include a WITH clause are called selective indexes.
Example
The following command defines a selective index named GT200 for file SALESTAFF, selecting items with attribute COMM greater than 200 and sorting the index by attribute LAST.NAME.
:DEFINE-INDEX SALESTAFF WITH COMM GT "200" BY LAST.NAMETO:GT200
After this command has executed, the index has been defined, but the index is not created until the CREATE-INDEX command is executed referencing index name GT200.
Caution
Selective Indexes should only be created after careful consideration of the alternatives. Used in the wrong way selective indexes can impose an unnecessary load on the system without providing any real benefits. In particular, you should never set up a set of selective indexes which between them cover all (or most) of the underlying file.
Incorrect use of selective indexes
The following are bad examples of the defining of selective indexes:
:DEFINE-INDEX PERSONNEL BY NAME WITH NAME LT "I"TO:IDX1
:DEFINE-INDEX PERSONNEL BY NAME WITH NAME GE "I" AND WITH NAME LT "R"TO:IDX2
:DEFINE-INDEX PERSONNEL BY NAME WITH NAME GE "R"TO:IDX3
These three indexes cover the wholepersonnel file and may give the impression that names can be located faster as you only have to search an index covering one third of the data.
However, in the above cases:
- Every time an item in the PERSONNEL file is updated, the Reality system code has to automatically keep all three indexes up to date. It will go through the whole business of constructing an index key and evaluating whether the key belongs in the index for each of these three indexes.
- English cannot automatically use a selective index to optimise an English query (see the section on Indexing in the English Reference).
- The total disk space occupied by the three indexes is probably greater than that occupied by a single index covering the whole file.
- The time saved in searching a smaller index is very small. Indexes are implemented as tree structures so the time taken to locate a specific entry depends mostly on the depth of the tree. In this specific instance an Index on the whole file would have a tree that was at most one level deeper than the selective indexes.
- What do you do if you need to select all the Smiths and Joneses in one operation? With separate indexes this is next to impossible. With a single index on the whole file it is easy.
In summary, selective Indexes are not necessarily bad, but please think carefully before creating them.
See also
Indexing, CREATE-INDEX, VERIFY-INDEX, DELETE-INDEX, EDELETE, HYPER-CREATE-INDEX.