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:

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:

For more information, see Database Performance and Query Optimization in the Information Center Link to Information center.