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.
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?.
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.
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$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$.
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.
--Run query against the base tables (with
Query Rewrite disabled on the cube)
ALTER MATERIALIZED VIEW
GLOBAL.CB$SALES_CUBE3 DISABLE QUERY REWRITE;
set autotrace traceonly explain
select
ch.channel_id channel,
sum(CAST(SALES AS NUMBER)) SALES,
sum(CAST(UNITS AS NUMBER)) UNITS
from
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
ch.channel_id;
Execution Plan
----------------------------------------------------------
0 SELECT
STATEMENT Optimizer=ALL_ROWS (Cost=925 Card=3 Bytes=9
6)
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)
6 5
TABLE ACCESS (FULL) OF 'UNITS_HISTORY_FACT' (TAB
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:
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:
--Run query against the base tables (but
with Query Rewrite enabled on the cube)
ALTER MATERIALIZED VIEW
GLOBAL.CB$SALES_CUBE3 ENABLE QUERY REWRITE;
set autotrace traceonly explain
select
ch.channel_id channel,
sum(CAST(SALES AS NUMBER)) SALES,
sum(CAST(UNITS AS NUMBER)) UNITS
from
GLOBAL.channel_dim ch,
GLOBAL.units_history_fact f
where ch.channel_id = f.channel_id
group by
ch.channel_id;
Execution Plan
----------------------------------------------------------
0 SELECT
STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=3 Bytes=30
9)
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.
set autotrace traceonly explain
SELECT *
FROM
TABLE(CUBE_TABLE(?GLOBAL.SALES2?));
Execution Plan
----------------------------------------------------------
0 SELECT
STATEMENT Optimizer=ALL_ROWS (Cost=29 Card=2000 Bytes
=200000)
1 0 CUBE SCAN
(PARTIAL OUTER) OF 'SALES2' (Cost=29 Card=2000 B
ytes=200000)
Conclusion
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.
and include the URL for the page.
