Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle CUBE

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 2n grouping sets.

When the aggregate function is the SUM() function, you will have 2n 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:

oracle cube example 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:

oracle cube - partial cube example

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle GROUPING SETS
Next Oracle ROLLUP

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.