If conditions are applied to single-column indexed columns, four cases have to be distinguished:
- EQUAL/IN condition:
If an EQUAL condition is specified for a single-column indexed column, only the rows with keys contained in the pertinent index list are accessed.
If an IN condition is specified, the rows with keys contained in the index lists are accessed.
- Several EQUAL conditions combined by AND:
If several EQUAL conditions are specified for different single-column indexed columns, an intersection of the corresponding index lists is formed. Only the rows with keys which are contained in all the indicated index lists are accessed.
- Conditions of ranges of values:
The specification of one condition ( '<', '<=', '>', '>=' ) on one of the two limits of the value range (lower or upper limit) will suffice for the selection of a search strategy.
If both limits are to be specified, it makes no difference to the search strategy whether this specification is made by one BETWEEN condition or by two conditions ('<=' or '>=') combined by AND defined for the same column.
The rows accessed are always the rows with keys that are contained in the index lists and that are designated by the range of values.
- Conditions of ranges of values combined by AND which are defined on ten columns:
If there is at least one value range restriction for one single-column indexed column and if there is one EQUAL condition or at least one value range restriction for each of up to 9 other single-column indexed columns, then the following action is taken:
One logical index list, which need not necessarily be physically available, is created for each of the available columns. An intersection is formed from all these index lists. Only the rows with keys contained in all index lists are accessed.
Examples:
Starting with the row with the key '123', the table is sequentially searched.
Starting with the key '123', the complete index list with the value 'Miller' is processed up to the end of the list.
A logical index list is created which contains all index lists of INVCOLUMN2 and which begins with a value less than 'C' (' ', 'A', 'B').
The intersection of the logical index list and of the list containing the value 'Miller' is formed and completely processed.
The intersection of both index lists is formed and completely processed.
The intersection of both index lists is formed and completely processed.
All index lists of invcolumn2 with values greater than 8965 are processed. The lists are, however, only considered between the key boundaries '34, 12' and '34, 18'.
For the EQUAL/IN condition and the conditions of ranges of values, there are some enquiries for which accessing the rows is not necessary, because all the required values are contained in the index list(s).