Indexing
Indexing is an optional feature which enables the creation and use of indexes in order to increase speed of access to file data. Reality processors, including English and DataBasic, can use indexes. Also, the TCL commands SELECT-INDEX and ISELECT generate active lists usable by TCL-II and English.
Refer to Indexing in User's Reference: General for details of the definition and creation of indexes.
Using SELECT-INDEX and ISELECT
SELECT-INDEX and ISELECT both generate an active item list from an index. You can specify individual key values, or ranges of key values.
Implicit Use of Indexes by English
Where a data section has associated indexes, English may use one of the indexes to access the data section. This increases the speed of execution by the English processor. In order for English to be able to use an index implicitly, the selection and sort criteria of the index must correspond to the English statement's selection or sort criteria, as follows:
Rules for Implicit Use of an Index
-
The index must contain all items in the file (its definition does not include selection criteria).
-
The selection in the English statement is on the item-id with the '=' connective: an index sorted primarily by item-id is then used. If selection criteria with a relational operator containing '<' or '>' are used, then an implicit index is not used.
-
The selection in the statement is on a specific attribute with the '=' connective: an index sorted primarily by that attribute is then used. If selection criteria with a relational operator containing '<' or '>' are used, then an implicit index is not used.
-
The selection in the statement must be based on a left-aligned complete or partial string.
-
If selection criteria are ANDed, either for the item-id or for the Data Definition Item, the use of the index is based on the left-most ANDed value.
-
The index is not used if there is ORed selection.
-
If there is no selection but the sort criteria matches an index definition exactly, the index is used.
Examples of Implicit Use of Indexes
In the following examples English uses a suitable index if one is available. Note that item-id selection always precedes and is implicitly ANDed with attribute selection criteria.
LIST FILE = '12]'
Uses an index that has no selection criteria, so that it contains all items
in the file, which is also sorted by
LIST FILE = '[12]'
Does not use an index as the selection criterion in the statement is not left-aligned. Rule 4 is obeyed.
LIST FILE = '12]' AND = '[X'
Uses an index as in the first example. Rules 1, 2, 4 and 5 are obeyed.
LIST FILE = '12]' = '3]'
Does not use an index as the item selection criteria in the statement are implicitly OR'd. Rule 6 is obeyed.
LIST FILE WITH DDI = "12]"
Uses an index that contains all file items, sorted primarily by attribute DDI. Rules 1 and 3 are obeyed.
LIST FILE = '12]' WITH DDI = "[ABC]"
Uses a complete index sorted primarily by item-id. Rules 1, 2, 4, and 5 are obeyed.
LIST FILE WITH DDI = "12]" AND = "[X"
Uses a complete index based primarily on attribute DDI. Rules 1, 3, 4, and 5 are obeyed.
LIST FILE WITH DDI = "12]" = "3"
Does not use an index as there is an implicit OR in the statement. Rule 6 is obeyed.
LIST FILE WITH DDI = "12]" AND WITH YYY = "ABC"
Uses a complete index sorted primarily on DDI. Rules 1, 3 and 5 are obeyed.
LIST FILE WITH DDI = "12]" WITH YYY = "ABC"
Does not use an index because the selection criteria are implicitly OR'd. Rule 6 is obeyed.
SORT FILE
Uses a complete index sorted primarily by item-id. Rules 1 and 7 are obeyed.
SORT FILE BY DDI
Uses a complete index sorted primarily on DDI. Rules 1 and 7 are obeyed.