GROUP BY WITH ROLLUP

This statement appears in the SHOWPLAN output for any query that contains a GROUP BY clause that specifies the ROLLUP option. Queries that contain a GROUP BY clause with the ROLLUP option will always be at least two-step queries: one step to select the qualifying rows into a worktable, and another step that sorts and groups these rows into a second worktable and then returns the rows from the second worktable.

The following example illustrates a two-step query that contains GROUP BY WITH ROLLUP:

Query:    SELECT stor_id, title_id, sum(qty)
          FROM sales
          GROUP BY stor_id, title_id
          WITH ROLLUP
  
SHOWPLAN: STEP 1
          The type of query is INSERT
          The update mode is direct
          FROM TABLE
          sales
          Nested iteration
          Table Scan
          TO TABLE
          Worktable 1
  
          STEP 2
          The type of query is SELECT
          FROM TABLE
          Worktable 2
                  GROUP BY WITH ROLLUP
                  Vector Aggregate
                  FROM TABLE
                  Worktable 1
                  Using GETSORTED Table Scan
                  TO TABLE
                  Worktable 2