ROLLUP and CUBE - Oracle DBA

Sometimes, a simple GROUP BY clause just isn't enough in a query. Once you generate a report of, let's say, average salary by department or the standard deviation of sick days by job title, you often must run a second query that calculates the average salary or standard deviation across the entire set of employees. It gets even more complex when you break down the average salary by more than one factor, such as department and job title. In this case, you would need to run two or more additional queries to produce the average salary just by department or for the entire workforce.

Tip The results from both CUBE and ROLLUP can be produced by multiple queries, but this requires multiple passes over the rows in the table. CUBE and ROLLUP need only one pass.

The ROLLUP operator provides rollups of aggregate functions in one direction across the fields that are aggregated. For each ROLLUP operation that uses n columns, the result set has aggregates for each combination of columns and n+1 groupings.

The CUBE operator takes the ROLLUP operator a step further and provides rollups of aggregate functions in both directions across the fields that are to be aggregated. For each CUBE operation that uses n columns, the result set has aggregates for each combination of columns plus 2n groupings.

ROLLUP

The boss asks Janice to give him a report that breaks down the average salary by both department and job function for departments 10 through 90. Janice wants to save time writing the query, and she knows by now that King will want to see some subtotals and grand totals. She will use ROLLUP to accomplish the task in a single query, as follows:

ROLLUP

25 rows selected.

Notice that because Janice has two columns listed in her ROLLUP clause, she will have three (two plus one) types of groupings in the query output:

  • Combinations of departments and jobs (for example, 30 and PU_CLERK, with an average salary of 2780)
  • Summaries by departments (for example, 20 and a NULL job title, with an average salary of 9500)
  • A grand total (NULL department number and NULL job title, with an average salary for all employees in all departments of 6250)

CUBE

The report that Janice wrote for King using the ROLLUP operator was fine—until he wanted to know summaries by job title also. Janice realizes that she should have given him the version of the query using CUBE to begin with, so she changes her previous query, substituting the keyword CUBE for ROLLUP:

CUBE

CUBE

40 rows selected.

Using CUBE, she has two columns listed in our ROLLUP clause and therefore will have four (two squared) types of groupings in the query output:

  • Combinations of departments and jobs (for example, 30 and PU_CLERK, with an average salary of 2780)
  • Summaries by jobs (for example, MK_REP having an average salary of 6000)
  • Summaries by departments (for example, 20 and a NULL job title, with an average salary of 9500)
  • A grand total (NULL department number and NULL job title, with an average salary for all employees in all departments of 6250)

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics