Oracle CUBE

Summary: in this tutorial, you will learn how to use the Oracle CUBE to generate grouping sets for all possible combinations of dimensions.

Introduction to Oracle CUBE expression

The CUBE is an extension of the GROUP BY clause that allows you to generate grouping sets for all possible combinations of dimensions.

The following illustrates the basic syntax of the CUBE with three columns (or dimensions):

SELECT c1, c2, c3, aggregate(c4)
FROM table_name
GROUP BY CUBE(c1,c2,c3);

In this syntax, the c1, c2, and c3 columns are called dimensions. The result of the aggregate(c4) aggregate function is known as a fact.

Typically, a fact is a number e.g., the sales amount. A dimension gives the fact a business context. For example, the product category and customer columns are dimensions that describe the sales amount such as total sales amount by product category and total sales amount by the customer.

The CUBE generates grouping sets of all combinations of c1, c2 and c3 dimensions, which returns 8 grouping sets.

In general, if you have n columns specified in the CUBE, you will get 2ⁿ grouping sets.

When the aggregate function is the SUM() function, you will have 2ⁿ subtotals for all the possible combinations of dimensions.

Oracle CUBE examples

See the following customer_category_sales view that returns sales amounts for all product categories and customers with the identity of 1 and 2:

CREATE OR ALTER VIEW customer_category_sales AS
SELECT 
    category_name category, 
    customers.name customer, 
    SUM(quantity*unit_price) sales_amount
FROM 
    orders
    INNER JOIN customers USING(customer_id)
    INNER JOIN order_items USING (order_id)
    INNER JOIN products USING (product_id)
    INNER JOIN product_categories USING (category_id)
WHERE 
    customer_id IN (1,2)
GROUP BY 
    category_name, 
    customers.name;

This example uses the CUBE to generate subtotals for product category and customer and grand total for these customers (customer id 1 and 2) and all product categories:

SELECT
    category,
    customer,
    SUM(sales_amount) 
FROM 
    customer_category_sales
GROUP BY 
    CUBE(category,customer)
ORDER BY 
    category NULLS LAST, 
    customer NULLS LAST;

Here is the output:

In this example, we have two dimensions: category and customer, therefore, the statement returns 4 subtotals:

• A subtotal by category.
• A subtotal by customer.
• A subtotal by both category and customer.
• A grand total.

Oracle allows you to reduce the number of generated grouping sets by using a partial cube as shown in the following syntax:

SELECT c1, c2, c3, aggregate(c4)
FROM table_name
GROUP BY c1, CUBE(c2,c3);

In this case, you will get 4 instead of 8 grouping sets.

For example, the following query uses a partial cube that generates subtotals for the product category dimension only:

SELECT
    category,
    customer,
    SUM(sales_amount) 
FROM 
    customer_category_sales
GROUP BY 
    category,
    CUBE(customer)
ORDER BY 
    category, 
    customer NULLS LAST; 

Here is the output:

In this tutorial, you have learned how to use the Oracle CUBE to generate grouping sets for all possible combinations of dimensions.