ROLLUP Extension to GROUP BY
ROLLUP enables a
SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total.
ROLLUP is a simple extension to the
GROUP BY clause, so its syntax is extremely easy to use. The
ROLLUP extension is highly efficient, adding minimal overhead to a query.
The action of
ROLLUP is straightforward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the
ROLLUP clause.
ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the
GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.
ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies
ROLLUP on grouping columns of
time,
region, and
department(n=3), the result set will include rows at four aggregation levels.
You might want to compress your data when using
ROLLUP. This is particularly useful when there are few updates to older partitions.
When to Use ROLLUP
Use the
ROLLUP extension in tasks involving subtotals.
- It is very helpful for subtotaling along a hierarchical dimension such as time or geography. For instance, a query could specify a
ROLLUP(y, m, day) or ROLLUP(country, state, city).
- For data warehouse administrators using summary tables,
ROLLUP can simplify and speed up the maintenance of summary tables.
ROLLUP Syntax
ROLLUP appears in the
GROUP BY clause in a
SELECT statement. Its form is:
SELECT … GROUP BY ROLLUP(grouping_column_reference_list)
CUBE Extension to GROUP BY
CUBE takes a specified set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis,
CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions. If you have specified
CUBE(time,
region,
department), the result set will include all the values that would be included in an equivalent
ROLLUP statement plus additional combinations. For instance, in
Figure 20-1, the departmental totals across regions (279,000 and 319,000) would not be calculated by a
ROLLUP(time,
region,
department) clause, but they would be calculated by a
CUBE(time,
region,
department) clause. If
n columns are specified for a
CUBE, there will be 2 to the
n combinations of subtotals returned.
Example 20-4 gives an example of a three-dimension cube. See
Oracle Database SQL Reference for syntax and restrictions.
When to Use CUBE
Consider
Using
CUBE in any situation requiring cross-tabular reports. The data needed for cross-tabular reports can be generated with a single
SELECT using
CUBE. Like
ROLLUP,
CUBE can be helpful in generating summary tables. Note that population of summary tables is even faster if the
CUBE query executes in parallel.
CUBE is typically most suitable in queries that use columns from multiple dimensions rather than columns representing different levels of a single dimension. For instance, a commonly requested cross-tabulation might need subtotals for all the combinations of month, state, and product. These are three independent dimensions, and analysis of all possible subtotal combinations is commonplace. In contrast, a cross-tabulation showing all possible combinations of year, month, and day would have several values of limited interest, because there is a natural hierarchy in the time dimension. Subtotals such as profit by day of month summed across year would be unnecessary in most analyses. Relatively few users need to ask "What were the total sales for the 16th of each month across the year?" See
"Hierarchy Handling in ROLLUP and CUBE" for an example of handling rollup calculations efficiently.
CUBE Syntax
CUBE appears in the
GROUP BY clause in a
SELECT statement. Its form is:
SELECT … GROUP BY CUBE (grouping_column_reference_list)
