Tips for grouping using index skip key processing
Index skip key processing can be used when grouping with the
keyed sequence implementation algorithm that uses an existing index. Index skip
key processing can be used for single file queries using the keyed sequence
grouping implementation when:
- There are no column functions in the query, or
- There is only a single MIN or MAX column function
in the query and the operand of the MIN or MAX is the next key column in the
index after the grouping columns. There can be no other grouping functions
in the query. For the MIN function, the key column must be an ascending key;
for the MAX function, the key column must be a descending key. If the query
is whole file grouping, the operand of the MIN or MAX must be the first key
column.
Example 1:
CREATE INDEX IX1 ON CORPDATA (SALARY DESC)
DECLARE C1 CURSOR FOR
SELECT MAX(SALARY) FROM CORPDATA
Example 2:
CREATE INDEX IX2 ON CORPDATA (DEPT, JOB,SALARY)
DECLARE C1 CURSOR FOR
SELECT DEPT, MIN(SALARY)
FROM CORPDATA
WHERE JOB='CLERK'
GROUP BY DEPT
Note: In Example 2, column
JOB is added as an implicit grouping column and, therefore, column SALARY is the
next key column in the index after the grouping columns.
For join queries:
- All grouping columns must be from a single file.
- For each dial there can be at most one MIN or MAX
column function operand that references the dial and no other column
functions can exist in the query.
- If the MIN or MAX function operand is from the same
dial as the grouping columns, then it uses the same rules as single file
queries.
- If the MIN or MAX function operand is from a
different dial then the join column for that dial must join to one of the
grouping columns and the index for that dial must contain the join columns
followed by the MIN or MAX operand.
For more information, see Database Performance and Query Optimization in the Information Center
.