GROUP BY WITH CUBE

This statement appears in the SHOWPLAN output for a query that contains a GROUP BY clause that specifies the WITH CUBE option. Queries that contain a GROUP BY clause with the WITH CUBE option will always be at least two-step queries: one step to select the qualifying rows into a worktable, and a second step to sort and group these rows (multiple times) into a second worktable and then return the rows from the second worktable.

The following example is of a two-step query that contains GROUP BY WITH CUBE:    

Query:    SELECT stor_id, title_id, sum(qty)
          FROM sales
          GROUP BY stor_id, title_id
          WITH CUBE
  
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 CUBE
                  Vector Aggregate
                  FROM TABLE
                  Worktable 1
                  Using GETSORTED Table Scan
                  TO TABLE
                  Worktable 2