Remote DBA Plans
Remote DBA Service

11g Before and After Enabling Query Rewrite on the Cube

Oracle 11g New Features Tips by Burleson Consulting
July 12, 2008

Oracle 11g SQL New Features Tips

Before enabling Query Rewrite on the Cube

--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=96)

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)

After enabling Query Rewrite on the Cube

Before the query against the base tables will rewrite, the Materialized Views will need to be refreshed by executing the dbms_mview.refresh procedure similar to the following:

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;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=3 Bytes=309)
   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 Card=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, but 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 Remote 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 Bytes=200000)

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.

To further expand the number of queries that can take advantage of materialized views Oracle can now rewrite queries on remote tables.

Conclusion

11g adds many new enhancements (482 to be precise), however to get the value from these improvements the Remote DBA must first learn the new features and then understand how to employ them to meet the needs of the business. 11g enables the Remote DBA to provide improved levels of service through the use of replication. For example the standby database (with Real-Time query) can be up on the weekends, while the Remote DBA is patching or upgrading the primary database, thus allowing the business to run reports. Replication such as Streams is up to two times faster with 11g, helping the Remote DBA to meet service level objectives. Another way 11g helps meet service level objectives is through the use of Enhanced Fast-Start Failover. The Remote DBA can failover for user configurable health conditions. This chapter covered some of the ways 11g DataGuard, Streams and other replication options have been enhanced to provide better High Availability.

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.