Optimizing grouping by using index skip key processing

 

Index Skip Key processing can be used when grouping with the keyed sequence implementation algorithm which uses an existing index. It is a specialized version of ordered grouping that processes very few records in each group rather than all records in each group.

The index skip key processing algorithm:

1. Uses the index to position to a group and

2. finds the first row matching the selection criteria for the group, and if specified the first non-null MIN or MAX value in the group

3. Returns the group to the user

4. "Skip" to the next group and repeat processing

This will improve performance by potentially not processing all index key values for a group.

Index skip key processing can be used:

• For single table 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 table grouping, the operand of the MIN or MAX must be the first key column.

    Example 1, using SQL:

    CREATE INDEX IX1 ON EMPLOYEE (SALARY DESC)
    
    
    DECLARE C1 CURSOR FOR
       SELECT MAX(SALARY) FROM EMPLOYEE;

    The query optimizer will chose to use the index IX1. The SLIC runtime code will scan the index until it finds the first non-null value for SALARY. Assuming that SALARY is not null, the runtime code will position to the first index key and return that key value as the MAX of salary. No more index keys will be processed.

    Example 2, using SQL:

    CREATE INDEX IX2 ON EMPLOYEE (WORKDEPT, JOB, SALARY)
    
    
    DECLARE C1 CURSOR FOR 
    	SELECT WORKDEPT, MIN(SALARY)
    		FROM EMPLOYEE 		WHERE JOB='CLERK'
    		GROUP BY WORKDEPT

    The query optimizer will chose to use Index IX2. The database manager will position to the first group for DEPT where JOB equals 'CLERK' and will return the SALARY. The code will then skip to the next DEPT group where JOB equals 'CLERK'.

• For join queries:

  • All grouping columns must be from a single table.

  • 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 table 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.

    Example 1, using SQL:

    CREATE INDEX IX1 ON DEPARTMENT(DEPTNAME)
    
    
    CREATE INDEX IX2 ON EMPLOYEE(WORKDEPT, SALARY)
    
    
    DECLARE C1 CURSOR FOR
       SELECT DEPARTMENT.DEPTNO, MIN(SALARY)
          FROM DEPARTMENT, EMPLOYEE       WHERE DEPARTMENT.DEPTNO=EMPLOYEE.WORKDEPT       GROUP BY DEPARTMENT.DEPTNO;
    

 

Parent topic:

Grouping optimization