Optimizing grouping by adding additional grouping columns

 

The same logic that is applied to removing grouping columns can also be used to add additional grouping columns to the query. This is only done when you are trying to determine if an index can be used to implement the grouping.

The following example illustrates a query where the optimizer might add an additional grouping column.

    CREATE INDEX X1 ON EMPLOYEE           (LASTNAME, EMPNO, WORKDEPT)
 
    DECLARE DEPTEMP CURSOR FOR       SELECT LASTNAME, WORKDEPT         FROM CORPDATA.EMPLOYEE         WHERE EMPNO = '000190'
        GROUP BY LASTNAME, WORKDEPT 

For this query request, the optimizer can add EMPNO as an additional grouping column when considering X1 for the query.

 

Parent topic:

Grouping optimization