This access method is very similar to the index scan-key selection access method. They both require a keyed sequence index. In the index scan-key selection access method, processing starts at the beginning of the index and continues to the end; all keys are paged in. In the index scan-key positioning access method, selection is against the index directly on a range of keys that match some or all of the selection criteria. Only those keys from this range are read and any remaining index scan-key selection is performed. This is similar to the selection performed by the index scan-key selection method. Any selection not performed through index scan-key positioning or index scan-key selection is performed at the dataspace level. Because index scan-key positioning only processes a subset of the keys in the index, the performance of the index scan-key positioning method is better than the performance of the index scan-key selection method.
Where the index scan-key positioning access method is most efficient
The index scan-key positioning method is most efficient when a small percentage of rows are to be selected (less than approximately 20%). If more than approximately 20% of the rows are to be selected, the optimizer generally chooses to:
How the query optimizer selects queries that use this method
For queries that do not require an index (no ordering, grouping, or join operations), the optimizer tries to find an existing index to use for index scan-key positioning. If no existing index can be found, the optimizer stops trying to use keyed access to the data because it is faster to use table scan processing than it is to build an index and then perform index scan-key positioning.
The following example illustrates a query where the optimizer could choose the index scan-key positioning method:
CREATE INDEX X1 ON EMPLOYEE(WORKDEPT)
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE WORKDEPT = 'E01'
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('WORKDEPT *EQ ''E01''')
In this example, the database support uses X1 to position to the first index entry with the WORKDEPT value equal to 'E01'. For each key equal to 'E01', it randomly accesses the dataspace 2 and selects the row. The query ends when the index scan-key selection moves beyond the key value of E01.
Note that for this example all index entries processed and rows retrieved meet the selection criteria. If additional selection is added that cannot be performed through index scan-key positioning (such as selection columns which do not match the first key columns of an index over multiple columns) the optimizer uses index scan-key selection to perform as much additional selection as possible. Any remaining selection is performed at the dataspace level.
The messages created by the PRTSQLINF CL command to describe this query in an SQL program would appear as follows:
SQL4008 Index X1 used for table 1. SQL4011 Key row positioning used on table 1.
The index scan-key positioning access method has additional processing capabilities. One such capability is to perform range selection across several values. For example:
CREATE INDEX X1 EMPLOYEE(WORKDEPT) DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE WHERE WORKDEPT BETWEEN 'E01' AND 'E11' OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('WORKDEPT *EQ %RANGE(''E01'' ''E11'')')
In the previous example, the database support positions to the first index entry equal to value 'E01' and rows are processed until the last index entry for 'E11' is processed.
PRTSQLINF command messages
The messages created by PRTSQLINF CL command to describe this query in an SQL program would appear as follows:
SQL4008 Index X1 used for table 1. SQL4011 Key row positioning used on table 1.
Multi-range index scan-key positioning
A further extension of this access method, called multi-range index scan-key positioning, is available. It allows for the selection of rows for multiple ranges of values for the first key columns of an index over multiple columns.
CREATE INDEX X1 ON EMPLOYEE(WORKDEPT)
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE WORKDEPT BETWEEN 'E01' AND 'E11'
OR WORKDEPT BETWEEN 'A00' AND 'B01'
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('WORKDEPT *EQ %RANGE(''E01'' ''E11'')
*OR WORKDEPT *EQ %RANGE(''A00'' ''B01'')')
In the previous example, the positioning and processing technique is used twice, once for each range of values.
The messages created by PRTSQLINF CL command to describe this query in an SQL program would appear as follows:
SQL4008 Index X1 used for table 1. SQL4011 Key row positioning used on table 1.
All of the index scan-key positioning examples have so far only used one key, the left-most key, of the index. Index scan-key positioning also handles more than one key (although the keys must be contiguous to the left-most key).
CREATE INDEX X2
ON EMPLOYEE(WORKDEPT,LASTNAME,FIRSTNME)
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
AND FIRSTNME = 'DAVID'
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('WORKDEPT *EQ ''D11''
*AND FIRSTNME *EQ ''DAVID''')
Because the two selection keys (WORKDEPT and FIRSTNME) are not contiguous, there is no multiple key position support for this example. Therefore, only the WORKDEPT = 'D11' part of the selection can be applied against the index (single index scan-key positioning). While this may be acceptable, it means that the processing of rows starts with the first key of 'D11' and then uses index scan-key selection to process the FIRSTNME = 'DAVID' against all 9 keys with WORKDEPT key value = 'D11'.
By creating the following index, X3, the above example query would run using multiple keys to do the 2index scan-key positioning.
CREATE INDEX X3
ON EMPLOYEE(WORKDEPT, FIRSTNME, LASTNAME)
Multiple index scan-key positioning support can apply both pieces of selection as index scan-key positioning. This improves performance considerably. A starting value is built by concatenating the two selection values into 'D11DAVID' and selection is positioned to the index entry whose left-most two keys have that value.
The messages created by the PRTSQLINF CL command when used to describe this query in an SQL program would look like this:
SQL4008 Index X3 used for table 1.
SQL4011 Key row positioning used on table 1.
This next example shows a more interesting use of multiple index scan-key positioning.
CREATE INDEX X3 ON EMPLOYEE(WORKDEPT,FIRSTNME)
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
AND FIRSTNME IN ('DAVID','BRUCE','WILLIAM')
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('WORKDEPT *EQ ''D11''
*AND FIRSTNME *EQ %VALUES(''DAVID'' ''BRUCE''
''WILLIAM'')')
The query optimizer analyzes the WHERE clause and rewrites the clause into an equivalent form:
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE (WORKDEPT = 'D11' AND FIRSTNME = 'DAVID')
OR (WORKDEPT = 'D11' AND FIRSTNME = 'BRUCE')
OR (WORKDEPT = 'D11' AND FIRSTNME = 'WILLIAM')
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('(WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ
''DAVID'')
*OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''BRUCE'')
*OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''WILLIAM'')')
In the rewritten form of the query there are actually 3 separate ranges of key values for the concatenated values of WORKDEPT and FIRSTNME:
Index X3 Start value Index X3 Stop value
'D11DAVID' 'D11DAVID'
'D11BRUCE' 'D11BRUCE'
'D11WILLIAM' 'D11WILLIAM'
Index scan-key positioning is performed over each range, significantly reducing the number of keys selected to just 3. All of the selection can be accomplished through index scan-key positioning.
The complexity of this range analysis can be taken to a further degree in the following example:
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE (WORKDEPT = 'D11'
AND FIRSTNME IN ('DAVID','BRUCE','WILLIAM'))
OR (WORKDEPT = 'E11'
AND FIRSTNME IN ('PHILIP','MAUDE'))
OR (FIRSTNME BETWEEN 'CHRISTINE' AND 'DELORES'
AND WORKDEPT IN ('A00','C01'))
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('(WORKDEPT *EQ ''D11''
*AND FIRSTNME *EQ %VALUES(''DAVID'' ''BRUCE'' ''WILLIAM''))
*OR (WORKDEPT *EQ ''E11''
*AND FIRSTNME *EQ %VALUES(''PHILIP'' ''MAUDE''))
*OR (FIRSTNME *EQ %RANGE(''CHRISTINE'' ''DELORES'')
*AND WORKDEPT *EQ %VALUES(''A00'' ''C01''))')
The query optimizer analyzes the WHERE clause and rewrites the clause into an equivalent form:
DECLARE BROWSE2 CURSOR FOR
SELECT * FROM EMPLOYEE
WHERE (WORKDEPT = 'D11' AND FIRSTNME = 'DAVID')
OR (WORKDEPT = 'D11' AND FIRSTNME = 'BRUCE')
OR (WORKDEPT = 'D11' AND FIRSTNME = 'WILLIAM')
OR (WORKDEPT = 'E11' AND FIRSTNME = 'PHILIP')
OR (WORKDEPT = 'E11' AND FIRSTNME = 'MAUDE')
OR (WORKDEPT = 'A00' AND FIRSTNME BETWEEN
'CHRISTINE' AND 'DELORES')
OR (WORKDEPT = 'C01' AND FIRSTNME BETWEEN
'CHRISTINE' AND 'DELORES')
OPTIMIZE FOR 99999 ROWS
OPNQRYF example:
OPNQRYF FILE((EMPLOYEE))
QRYSLT('(WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ
''DAVID'')
*OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ ''BRUCE'')
*OR (WORKDEPT *EQ ''D11'' *AND FIRSTNME *EQ
''WILLIAM'')
*OR (WORKDEPT *EQ ''E11'' *AND FIRSTNME *EQ ''PHILIP'')
*OR (WORKDEPT *EQ ''E11'' *AND FIRSTNME *EQ ''MAUDE'')
*OR (WORKDEPT *EQ ''A00'' *AND
FIRSTNME *EQ %RANGE(''CHRISTINE'' ''DELORES''))
*OR (WORKDEPT *EQ ''C01'' *AND
FIRSTNME *EQ %RANGE(''CHRISTINE'' ''DELORES''))')
In the query there are actually 7 separate ranges of key values for the concatenated values of WORKDEPT and FIRSTNME:
Index X3 Start value Index X3 Stop value 'D11DAVID' 'D11DAVID' 'D11BRUCE' 'D11BRUCE' 'D11WILLIAM' 'D11WILLIAM' 'E11MAUDE' 'E11MAUDE' 'E11PHILIP' 'E11PHILIP' 'A00CHRISTINE' 'A00DELORES' 'C01CHRISTINE' 'C01DELORES'
Index scan-key positioning is performed over each range. Only those rows whose key values fall within one of the ranges are returned. All of the selection can be accomplished through index scan-key positioning. This significantly improves the performance of this query.