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 







Using Cube Oriented Materialized Views

Oracle Tips by Brian Carr
August 6,  2015

Oracle has been adding new features to ANSI SQL over the years.  CUBE and ROLLUP were new in 8i, GROUPING SETS were added in 9i, enhancements to the MERGE statement in 10g.  With 11g Cube Organized Materialized Views are arguably the best yet.  This article highlights this feature and gives an overview of its benefits.

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.

 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. 

Today the DBA must basically know the SQL that users will throw at the database.  With 11g a new feature is Cube Organized Materialized Views.  Instead of dozens of Materialized Views you could theoretically have one Cube Organized Materialized View.  This new feature in 11g also gives applications, which normally wouldn't be able to take advantage of cubes (i.e. 3rd-party ad-hoc query tools which cannot be rewritten), to now use this powerful feature.  The new query rewrite with 11g automatically rewrites the SQL to take advantage of the cube.

Inside the Oracle 11g Cube organized materialized views

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 against the base tables.  The query is then rewritten to access the cube organized materialized view via the query rewrite 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.  Cube organized materialized views does not replicate or store data, rather they are metadata objects that access data from the OLAP cube.  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 data type.


Creating a Cube Organized Materialized View

To see how a cube organized materialized view allows for transparent rewrite of queries against the source tables lets create an example using the standard GLOBAL Sales cube.

The DBA can use the 11g Analytic Workspace Manager to enable a cube for transparent rewrite capability.  First drill down to the cube that needs to have rewrite enabled.  Then click on the Materialized View tab as shown in Figure 1 below.

Figure 1

Notice the warning given by AWM in Figure 1 above.  This is telling the DBA that the cube must be compressed in order to take advantage of query rewrite.  Figure 2 below shows what I like to call 'the right way?.

Figure 2


Once all the Compatibility Checklist warnings are resolved, the DBA will select ?Enable Materialized View Refresh of the Cube?.  Choose the desired refresh options, and then click the ?Enable Query Rewrite? and Apply as shown in Figure 3 below.

To obtain a listing of the Cube Organized Materialized Views in the database the DBA can run the query in Listing 1 below.

Listing 1

column object_name heading 'Object' format a30
column object_type heading 'Object Type' format a25
select object_name,object_type
from dba_objects
where object_name like 'CB$%';
Object                                                               Object Type
------------------------------ ------------------------------------------------- -----------------
CB$CUSTOMER_SHIPMENTS                            TABLE
CB$SALES_CUBE3                                           TABLE
CB$SALES_CUBE3_1                                       TABLE
CB$SALES_CUBE3_1                                       MATERIALIZED VIEW
CB$TIME_CALENDAR_YEAR                           TABLE


All Cube Organized Materialized Views are prefixed with CB$.


Before enabling Query Rewrite on the Cube

Now that the DBA understands how to enable a cube for query rewrite, let's compare the execution plan of one query before and after query rewrite has been enabled.  Listing 2 below illustrates the execution plan with query rewrite disabled.

Listing 2

--Run query against the base tables (with Query Rewrite disabled on the cube)
set autotrace traceonly explain
ch.channel_id channel,
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=925 Card=3 Bytes=9
   1    0   SORT (GROUP BY NOSORT) (Cost=925 Card=3 Bytes=96)
   2    1     MERGE JOIN (Cost=925 Card=3 Bytes=96)
   3    2       SORT (JOIN) (Cost=921 Card=3 Bytes=87)
   4    3         VIEW OF 'VW_GBC_5' (VIEW) (Cost=921 Card=3 Bytes=87)
   5    4           HASH (GROUP BY) (Cost=921 Card=3 Bytes=45)
          LE) (Cost=882 Card=885988 Bytes=13289820)
   7    2       SORT (JOIN) (Cost=4 Card=3 Bytes=9)
   8    7         TABLE ACCESS (FULL) OF 'CHANNEL_DIM' (TABLE) (Cost=3
           Card=3 Bytes=9)


In Listing 2 above the query performed a full table scan on the base table.


After enabling Query Rewrite on the Cube

Now let's examine the execution plan with query rewrite enabled.  However, before the query against the base tables will rewrite the Materialized Views will need refreshed by executing the dbms_mview.refresh procedure as shown in Listing 3:

Listing 3

exec dbms_mview.refresh ('CB$CUSTOMER_MARKET_SEGMENTS','CF');
exec dbms_mview.refresh ('CB$CHANNEL_PRIMARY','CF');
exec dbms_mview.refresh ('CB$CUSTOMER_SHIPMENTS','CF');
exec dbms_mview.refresh ('CB$PRODUCT_PRIMARY','CF');
exec dbms_mview.refresh ('CB$TIME_CALENDAR_YEAR','CF');
exec dbms_mview.refresh ('CB$SALES_CUBE3','CF');

To test if queries against the base tables are rewritten against the Cube Organized Materialized Views run an explain plan:

Listing 4

--Run query against the base tables (but with Query Rewrite enabled on the cube)
set autotrace traceonly explain
ch.channel_id channel,
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=3 Bytes=30
   1    0   HASH (GROUP BY) (Cost=34 Card=3 Bytes=309)
   2    1     HASH JOIN (Cost=33 Card=60 Bytes=6180)
   3    2       TABLE ACCESS (FULL) OF 'CHANNEL_DIM' (TABLE) (Cost=3 C
          ard=3 Bytes=9)
   4    2       CUBE SCAN (PARTIAL OUTER) OF 'CB$SALES_CUBE3' (Cost=29
           Card=2000 Bytes=200000)


The above SQL was written to query the UNITS_HISTORY_FACT table directly, however due to Cube Organized Materialized Views it was rewritten behind-the-scenes to query against the cube.  The execution plan above is similar to the following SQL where the DBA wrote the SQL to specifically query against an OLAP cube.


Listing 5

set autotrace traceonly explain
Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=2000 Bytes
   1    0   CUBE SCAN (PARTIAL OUTER) OF 'SALES2' (Cost=29 Card=2000 B


The advantage to Cube Organized Materialized View is realized in the above examples where a query that normally would have queried against the relational table was seamlessly rewritten to access the OLAP cube without making any changes to the SQL.  Thus applications which are impossible or difficult to alter can take advantage of OLAP cubes without altering a single line of code.

For more details on these exciting new Oracle Database 11g features, see my book ?Oracle 11g New Features?.

Brian Carr works as a Senior Oracle DBA and is an Oracle Certified Professional and Oracle ACE. His articles have appeared in SELECT Journal, OTN, Database Trends and Applications, JavaWorld, and DevX. He co-wrote a multithreaded Web crawler and search engine in Perl, as well as an anonymous web browsing service in C#. Brian also wrote a server monitoring solution that has been commercially released.  Brian is currently pursuing a Master's degree. 




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.