Tips for eliminating grouping columns

All of the grouping columns are evaluated to determine if they can be removed from the list of grouping columns. Only those grouping columns that have isolatable selection predicates with an equal operator specified can be considered. This guarantees that the column can only match a single value and will not help determine a unique group. This processing is done to allow the optimizer to consider more indexes to implement the query and to reduce the number of columns that will be added as key columns to any temporary result used to implement the grouping.

The following example illustrates a query where the optimizer could eliminate a grouping column:

 CREATE INDEX X1 ON EMPLOYEE (LASTNAME, WORKDEPT)

    DECLARE DEPTEMP CURSOR FOR
      SELECT EMPNO, LASTNAME, WORKDEPT
        FROM CORPDATA.EMPLOYEE
        WHERE EMPNO = '000190'
        GROUP BY EMPNO, LASTNAME, WORKDEPT

In this example, the optimizer can remove EMPNO from the list of grouping columns because of the EMPNO = '000190' selection predicate. An index that has only LASTNAME and WORKDEPT specified as key columns can be used to implement the query. If a temporary result is required then EMPNO will not be used.

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