11g Cube Organized Materialized Views Oracle 11g New Features Tips by Burleson Consulting July 12, 2008

Oracle 11g SQL New Features Tips

Materialized Views

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 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 11.1.0.6.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. 3^rd-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 (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.  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
select object_name,object_type
from Remote DBA_objects
where object_name like 'CB$%';

Object                         Object Type
------------------------------ -----------------
CB$CHANNEL_PRIMARY             TABLE
CB$CHANNEL_PRIMARY             MATERIALIZED VIEW
CB$CUSTOMER_MARKET_SEGMENTS    TABLE
CB$CUSTOMER_MARKET_SEGMENTS    MATERIALIZED VIEW
CB$CUSTOMER_SHIPMENTS          TABLE
CB$CUSTOMER_SHIPMENTS          MATERIALIZED VIEW
CB$PRODUCT_PRIMARY             TABLE
CB$PRODUCT_PRIMARY             MATERIALIZED VIEW
CB$SALES_CUBE3                 TABLE
CB$SALES_CUBE3_1               TABLE
CB$SALES_CUBE3_1               MATERIALIZED VIEW
CB$TIME_CALENDAR_YEAR          TABLE
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.