11g Cube Organized Materialized Views
Oracle 11g New Features Tips by Burleson
July 12, 2008
Oracle 11g SQL New Features Tips
The focus of this chapter is on Streams,
Replication and Data Guard. Materialized Views are a form of
replication and can allow the Remote DBA to maintain copies of data from
remote databases on the local system. Materialized views are also
used for the purposes of pre-aggregating data.
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.
Additionally, 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
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 126.96.36.199.0 - 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.
Today the Remote 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
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
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 datatype.
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 old standard GLOBAL Sales cube.
The Remote 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 9.12 below.
Notice the warning given by AWM in Figure 9.12 above. This is
telling the Remote DBA that the cube must be compressed in order to take
advantage of query rewrite. Figure 9.13 below shows what I like to
call “the right way”.
Once all the Compatibility Checklist warnings are resolved, the
Remote 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 9.14 below.
To see the Cube Organized Materialized Views in the database the
Remote DBA can run the query:
column object_name heading 'Object' format a30
column object_type heading 'Object Type' format a25
from Remote DBA_objects
where object_name like 'CB$%';
Object Object Type
CB$CHANNEL_PRIMARY MATERIALIZED VIEW
CB$CUSTOMER_MARKET_SEGMENTS MATERIALIZED VIEW
CB$CUSTOMER_SHIPMENTS MATERIALIZED VIEW
CB$PRODUCT_PRIMARY MATERIALIZED VIEW
CB$SALES_CUBE3_1 MATERIALIZED VIEW
CB$TIME_CALENDAR_YEAR MATERIALIZED VIEW
All Cube Organized Materialized Views are
prefixed with CB$.
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off.