Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








11g Cube-organized Materialized View tips

Oracle Database Tips by Donald BurlesonNovember 14, 2015


This is an excerpt from the book "Oracle 11g New Features" authored by John Garmany, Brian Carr, and V. J. Jain, with Oracle ACE's Steve Karam and Lutz Hartmann.

Inside the Oracle 11g Cube organized materialized views

Oracle's acquisition of Hyperion's OLAP software now gives Oracle access to the powerful Essbase engine, which will be integrated with the legacy ?Oracle Express? technology that Oracle acquired from IRI software.

Cube organized materialized views have extra costs

Many extra-cost components have moved inside the 11g kernel software and are tightly integrated to the Oracle11g RDBMS engine.  Because of their tightly-coupled nature, they are installed by Oracle 11g by default, add-on tools such as the Automatic Workload Repository, components of the Oracle 11g BI Suite, Oracle Data Mining (ODM), and the Oracle warehouse builder (OWB).

These are now available by default, but you must be cognizant that these are extra-cost features and their usage can be audited by Oracle Corporation.

Oracle Database 11g Enterprise Edition Release - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options


Using cube organized materialized views

Oracle has devised a way to use Oracle's materialized view construct to store OLAP cubes, much in the same fashion as materialized views are used to pre-join tables and pre-aggregate table data. 

The Oracle 11g BI documentation notes the interface layer between the conceptual star schema and OLAP cube, and the underlying data storage layer:

In this use of the cube, summary data is managed within the cube and revealed to the application as a cube-organized materialized view. The application continues to query the detailed level data in relational tables, expressing queries for summary level data with an aggregation function and GROUP BY.

The automatic query rewrite feature of the Oracle Database automatically rewrites the query to the cube organized materialized view. This application benefits from improved query performance.

Inside the 11g Business Intelligence Suite, these OLAP cubes are the underlying representation of Oracle's multidimensional star schema.

  • Cube-organized materialized views can be accessed by standard SQL queries (with the pivot syntax), the base tables being accessed via the query re-write mechanism.   Cube-organized materialized views can also be accessed via the Oracle Express traditional dimensional queries.
  • Cube-organized materialized views are supported within RAC/Grid.
  • OLAP cubes are presented as cube-organized materialized views and can be integrated into a star schema.
  • Cube-organized materialized views cannot be used with transportable tablespaces.

Access to the cube-organized materialized views is available directly within Oracle SQL.  Oracle 11g SQL has a new ?pivot? SQL operator, replacing the decode function for querying multidimensional data

It uses the same query re-write mechanism, and has the same automatic update mechanism to keep the OLAP cube ?fresh? as data changes within the underlying dimensions.  The refresh mechanism offers several refresh mechanisms:

  • Instant cube-organized materialized view refreshing ("on commit" using dbms_mview.refresh)

  • Refresh after a pre-defined threshold of 'tolerated staleness? is exceeded.

  • A scheduled refresh, often performed hourly, daily or weekly.

As an example of a cube-organized materialized view, consider the daily collection of sales summary data by region.  This is a standard two-dimensional table, with sales rows, and distinct columns for each region. 

When we add-in the time dimensional, the representation becomes cubic, or three dimensional.  In practice, the third-dimension of an OLAP cube is often a DATE datatype.

Laurent Schneider, co-author of the book "Oracle 11g New Features" gives an example of using the "pivot" operator to replace the archaic decode syntax:

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

---------- ----- --------------
        30 DNAME SALES
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
pivot (max(value)
for type in (
  ?gender? as gender,
  ?category? as category))
where = property.person (+);

and with the unpivot keyword

select *
from dept
unpivot (value
for type in (
  dname as ?DNAME?,
  loc as ?LOC?));




If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.