Oracle OLAP 11g: Setting Up Cube Materialized Views for Query Rewrite

Purpose

This tutorial describes how to enable cubes as Cube Materialized Views, and how to enable and troubleshoot Query Rewrite using Analytic Workspace Manager 11.1.0.7. It is intended as a quickstart for intermediate developers. Note, some of the images may be too small to read the detail; if you print this document, you can read the detail.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

	Overview
	What are Query Rewrite and Cube Materialized Views?
	Cube MV Requirements
	How to set up a Cube MV
	Refresh Methods
	Enabling Query Rewrite
	Refreshing the Cube MV
	SQL Execution Compared
	Troubleshooting Query Rewrite
	Final Notes
	Summary
	Appendix

Overview

In Oracle 11g, OLAP cubes can be enabled to be used as Materialized Views (MVs) and using these MVs, SQL applications can use data stored in OLAP completely transparently. This note describes how to take a pre-existing OLAP cube, enable it as a Materialized View and enable for Query Rewrite.

Refer to Oracle OLAP User's Guide 11g Part Number B28124-02 for further information.

Back to Topic List

What are Query Rewrite and Cube Materialized Views?

A Materialized View (MV) is a Database object which holds a snapshot of data, usually a store of preaggregated data. Query Rewrite is a query optimization technique that transforms a user query written in SQL, to execute faster by fetching data from MVs.. It requires no intervention or hints in the SQL application because the Oracle Database automatically rewrites any appropriate SQL query to use the MVs even if the MV can only satisfy part of the query.

Cube MVs are a new form of MV for Oracle Database 11g; the data for a cube MV is stored in an OLAP cube instead of a relational table (which is how relational MVs are organized). A Cube MV inherits all the query and refresh performance benefits of the cube and is transparently available to SQL applications. The cube inherits the refresh maintenance benefits of MVs.

Back to Topic List

Cube MV Requirements

If  you wish to use your OLAP cubes transparently as  a performance booster for your SQL application, you can enable your OLAP cubes as Materialized Views and enable Query Rewritein Anaytic Workspace Manager (AWM). Your cube must conform to the following requirements to be enabled for MV/Query Rewrite:

• All dimensions of the cube have at least one level and one hierarchy. Ragged, skip-level and value based hierarchies are not supported.
   
• All dimensions of the cube use the same aggregation operator, which is either SUM, MIN, or MAX.
   
• The cube is fully defined and mapped. For example, if the cube has five measures, then all five are mapped to the source tables. The cube can be enriched with calculated measures, which cannot be exposed via MVs.  A MV cannot be defined if there are  advanced analytics in a cube script.
   
• Key constraints need to be defined on the dimension and fact tables (FK constraints at least). If they have not been defined, then you can use the Relational Schema Advisor to generate a script that defines them on the detail tables once you have enabled for MVs.
• All constraints on the table need to be of the same type
• The cube is compressed.
• For better Query Rewrite coverage, you should create relational dimension objects.

Back to Topic List

How to set up a Cube MV

In this example, the sales_cube cube is already created, and measures are defined: mapped measures and simple calculated measures (there are no advanced analytics/programs in these calculations). This cube is 4 dimensional, partitioned on month, is compressed, and has multiple measures which are mapped to a fact table, and some calculated measures.

1. To create the Materialized View for this cube, and enable for Query Rewrite, highlight the existing cube, and click on the MV tab. (Note, you can also specify the MV options whilst you are defining the cube).

2. Check status of the compatibility checklist, all items need to be valid before you can enable for MVs, or for Query Rewrite.

2. Check the 'Enable MV' checkbox to enable for MV. Note, this will allow the cube to  take advantage of the MV refresh system for data maintenance whether or not you will use MV Query Rewrite.

Refresh Methods

The 'default' refresh methods available in AWM are:

1. Complete - deletes all existing data, reloads and aggregates

2. Fast - uses the MV log system to identify which records have been changed, and loads and agggregates those records. Should be useful if updating data all over the cube.

3. Force- will use Fast as default, and then Complete if Fast unavailable

Other available methods which can overwrite the saved default method are:

4. Partition Change Tracking: Clears, loads, and aggregates only the values from an altered partition in the source tables. Should be useful if you're only updating a partition of data and not using query rewrite ( ie just using MR system to keep data automatically refreshed in the cubes). This is not an option in AWM

5. Fast Solve: Loads all the detail data from the source tables, then aggregates only the new values. This is applicable only for MVs, and is not an option in AWM.

Even if you don't want to use MVs and Query Rewrite and your model has the characterisitcs to allow MVs to be built (ie specific aggregation types and no advanced calculations), you may take advantage of the refresh features of MVs to maintain your cube. Having the different refresh capabilities means full and incremental maintenance can be efficiently managed by the Database.

Refresh Mode and Constraints will be explained in later sections.

After enabling, the following details about the MV can be seen in the cube/MV details tab:

The MV that will be created will materialize the mapped measures, but not the calculated measures. Also, although an MV will be created for most models, when the size of the Materialized View gets very large, the time to create it will become longer. In addition, if you have large cubes with lots of levels, you should think about creating sub cubes and having smaller materialized views. You may also remove some attributes from your MV, if you do not intend always using them; to do this, when defining the dimension in AWM you can uncheck the 'create level attribute columns in views' checkbox.

MV objects are created for the dimensions and cubes; one for each dimension hierarchy combination, and one of the cube; MV names  are prefixed with CB$ which can be seen in the MV Compatibility Checklist above. There is one additional MV object created for the cube which is prefixed with CR$. This is used for MV refresh. You will not to access these MV objects directly and you should not modify their structure, although you should be aware of their name and contents when checking that Query Rewrite is working. You can also get a list of the MVs by running the following SQL:

select owner||'.'||mview_name cube_mv,rewrite_enabled,staleness
from all_mviews where container_name like 'CB$%';

The contents of the CB$ MV can be seen by running a desc on the object:

SQL> desc cb$sales_cube;
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
SYS_GID NUMBER
CHANNEL VARCHAR2(40)
D1_CHANNEL_CLASS_ID NUMBER
D1_CLASS_LONG_DESCRIPTION VARCHAR2(20)
D1_CLASS_SHORT_DESCRIPTION VARCHAR2(20)
D1_CHANNEL_CHANNEL_ID NOT NULL NUMBER
D1_CHANNEL_LONG_DESCRIPTION VARCHAR2(20)
D1_CHANNEL_SHORT_DESCRIPTIO VARCHAR2(20)
D1_CHANNEL_CHANNEL_TYPE VARCHAR2(30)
TIME VARCHAR2(30)
D2_ALL_YEARS_END_DATE DATE
D2_TIME_CALENDAR_YEAR_ID NOT NULL VARCHAR2(30)
D2_CALENDAR_YEAR_END_DATE DATE
D2_CALENDAR_YEAR_TIME_SPAN NUMBER
D2_CALENDAR_YEAR_LONG_DESCR VARCHAR2(40)
D2_CALENDAR_YEAR_SHORT_DESC VARCHAR2(40)
D2_TIME_CALENDAR_QUARTER_ID NOT NULL VARCHAR2(30)
D2_CALENDAR_QUARTER_END_DAT DATE
D2_CALENDAR_QUARTER_TIME_SP NUMBER
D2_CALENDAR_QUARTER_LONG_DE VARCHAR2(40)
D2_CALENDAR_QUARTER_SHORT_D VARCHAR2(40)
D2_TIME_MONTH_ID NOT NULL VARCHAR2(30)
D2_MONTH_END_DATE DATE
D2_MONTH_TIME_SPAN NUMBER
D2_MONTH_LONG_DESCRIPTION VARCHAR2(40)
D2_MONTH_SHORT_DESCRIPTION VARCHAR2(40)
GEOGRAPHY VARCHAR2(40)
D3_GEOGRAPHY_REGION_ID NUMBER
D3_REGION_LONG_DESCRIPTION VARCHAR2(100)
D3_REGION_SHORT_DESCRIPTION VARCHAR2(100)
D3_GEOGRAPHY_COUNTRY_ID NUMBER
D3_COUNTRY_LONG_DESCRIPTION VARCHAR2(100)
D3_COUNTRY_SHORT_DESCRIPTIO VARCHAR2(100)
D3_GEOGRAPHY_STATE_PROVINCE NUMBER
D3_STATE_PROVINCE_LONG_DESC VARCHAR2(400)
D3_STATE_PROVINCE_SHORT_DES VARCHAR2(400)
PRODUCT VARCHAR2(40)
D4_PRODUCT_DEPARTMENT_ID NOT NULL NUMBER
D4_DEPARTMENT_LONG_DESCRIPT VARCHAR2(100)
D4_DEPARTMENT_SHORT_DESCRIP VARCHAR2(100)
D4_PRODUCT_CATEGORY_ID NOT NULL NUMBER
D4_CATEGORY_LONG_DESCRIPTIO VARCHAR2(100)
D4_CATEGORY_SHORT_DESCRIPTI VARCHAR2(100)
D4_PRODUCT_TYPE_ID NOT NULL NUMBER
D4_TYPE_LONG_DESCRIPTION VARCHAR2(100)
D4_TYPE_SHORT_DESCRIPTION VARCHAR2(100)
D4_PRODUCT_SUBTYPE_ID NOT NULL NUMBER
D4_SUBTYPE_LONG_DESCRIPTION VARCHAR2(100)
D4_SUBTYPE_SHORT_DESCRIPTIO VARCHAR2(100)
D4_PRODUCT_ITEM_ID NOT NULL NUMBER
D4_ITEM_LONG_DESCRIPTION VARCHAR2(100)
D4_ITEM_SHORT_DESCRIPTION VARCHAR2(100)
SALES NUMBER
QUANTITY NUMBER
SYS_COUNT NUMBER

Back to Topic List

Enabling Query Rewrite

To allow applications to make use of these Materialized Views, you must grant Query Rewrite privileges to the user, and turn on Query Rewrite. To do this:

1. Grant CREATE Materialized View privilege to user; and ensure appropriate privileges on the objects are set. Refer to Metalink Doc Id for more information on privileges needed for 11g OLAP users.
2. Enable Query Rewrite at the database level most commonly at the Database level. In init.ora set QUERY_REWRITE_ENABLED= TRUE which will allows a cost based estimation to be done or QUERY_REWRITE_ENABLED=FORCE. You may also set these at the session level.
3. Check the Enable Query Rewrite checkbox as illustrated above. You may see some more checks appear in the check list.
4. Click on the Relational Schema Advisor button to give you details of other changes to the schema that you should make to increase the chance of Query Rewrite being invoked. Note, if you have not already created constraints on your base tables, this script will include the commands to do so.

The output from the Relational Schema Advisor can be saved to SQL script, and may give a large output. Remove all the commented lines from the output, and run the remainder of script within the specified schema. For example, here is the summary output from this being run on the SALES_CUBE once the commented lines are removed:

REM **********************************************************
REM Relational Schema Advisor SQL Script
REM Script file created on 11-12-2008 10:33:44
REM Script created by database user dm
REM Database Instance 11ginstance:1521:orcl11g
REM Analytic Workspace SALESTRACK
REM OLAP Cube DM.SALES_CUBE
REM Materialized View DM.CB$SALES_CUBE
REM **********************************************************

-- Run the following SQL Commands to Drop Relational Schema Advice
drop dimension "DM"."CHANNEL";
drop dimension "DM"."GEOGRAPHY";
drop dimension "DM"."PRODUCT";
alter table "DM"."CHANNELS" drop constraint "COAD_NN000101" cascade;
alter table "DM"."CHANNELS" drop constraint "COAD_NN000102" cascade;
alter table "DM"."CHANNELS" drop constraint "COAD_NN000103" cascade;
alter table "DM"."CHANNELS" drop constraint "COAD_PK000124" cascade;
drop materialized view log on "DM"."CHANNELS";
drop materialized view log on "DM"."CHANNELS";
alter table "DM"."COUNTRIES" drop constraint "COAD_NN000104" cascade;
drop materialized view log on "DM"."COUNTRIES";
drop materialized view log on "DM"."COUNTRIES";
alter table "DM"."CUSTOMERS" drop constraint "COAD_NN000105" cascade;
alter table "DM"."CUSTOMERS" drop constraint "COAD_PK000122" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000106" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000107" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000108" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000109" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000110" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_NN000111" cascade;
alter table "DM"."PRODUCTS" drop constraint "COAD_PK000123" cascade;
drop materialized view log on "DM"."PRODUCTS";
drop materialized view log on "DM"."PRODUCTS";
alter table "DM"."REGIONS" drop constraint "COAD_NN000112" cascade;
drop materialized view log on "DM"."REGIONS";
drop materialized view log on "DM"."REGIONS";
alter table "DM"."SALES_FACT" drop constraint "COAD_NN000113" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_NN000114" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_NN000115" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_NN000116" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_PK000126" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_FK000127" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_FK000130" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_FK000128" cascade;
alter table "DM"."SALES_FACT" drop constraint "COAD_FK000129" cascade;
drop materialized view log on "DM"."SALES_FACT";
drop materialized view log on "DM"."SALES_FACT";
alter table "DM"."STATE_PROVINCES" drop constraint "COAD_NN000117" cascade;
drop materialized view log on "DM"."STATE_PROVINCES";
drop materialized view log on "DM"."STATE_PROVINCES";
alter table "DM"."TIMES" drop constraint "COAD_NN000118" cascade;
alter table "DM"."TIMES" drop constraint "COAD_NN000119" cascade;
alter table "DM"."TIMES" drop constraint "COAD_NN000120" cascade;
alter table "DM"."TIMES" drop constraint "COAD_NN000121" cascade;
alter table "DM"."TIMES" drop constraint "COAD_PK000125" cascade;
drop materialized view log on "DM"."TIMES";
drop materialized view log on "DM"."TIMES";
drop dimension "DM"."TIME";
alter materialized view "DM"."CB$SALES_CUBE" compile;
alter table "DM"."CHANNELS" modify ( "CHANNEL_KEY" constraint "COAD_NN000101" not null enable validate  );
alter table "DM"."CHANNELS" modify ( "CLASS_KEY" constraint "COAD_NN000102" not null enable validate  );
alter materialized view log force on "DM"."COUNTRIES" add primary key, rowid, sequence ("COUNTRY_NAME") including new values;
alter table "DM"."PRODUCTS" modify ( "CATEGORY_KEY" constraint "COAD_NN000106" not null enable validate  );
alter table "DM"."PRODUCTS" modify ( "DEPARTMENT_KEY" constraint "COAD_NN000107" not null enable validate  );
alter table "DM"."PRODUCTS" modify ( "ITEM_KEY" constraint "COAD_NN000109" not null enable validate  );
alter table "DM"."PRODUCTS" modify ( "SUBTYPE_KEY" constraint "COAD_NN000110" not null enable validate  );
alter table "DM"."PRODUCTS" modify ( "TYPE_KEY" constraint "COAD_NN000111" not null enable validate  );
alter table "DM"."SALES_FACT" add constraint "COAD_PK000126" PRIMARY KEY (CHANNEL, DAY_KEY, PRODUCT, CUSTOMER) norely enable validate ;
alter table "DM"."SALES_FACT" modify ("CUSTOMER" constraint "COAD_FK000127" references "DM"."CUSTOMERS"("CUSTOMER_KEY") norely enable validate  );
alter table "DM"."SALES_FACT" modify ("DAY_KEY" constraint "COAD_FK000130" references "DM"."TIMES"("DAY_KEY") norely enable validate  );
alter table "DM"."SALES_FACT" modify ("PRODUCT" constraint "COAD_FK000128" references "DM"."PRODUCTS"("DIMENSION_KEY") norely enable validate  );
alter table "DM"."SALES_FACT" modify ("CHANNEL" constraint "COAD_FK000129" references "DM"."CHANNELS"("DIMENSION_KEY") norely enable validate  );
alter materialized view log force on "DM"."STATE_PROVINCES" add primary key, rowid, sequence ("STATE_PROVINCE_NAME") including new values;
alter table "DM"."TIMES" modify ( "CALENDAR_QUARTER_ID" constraint "COAD_NN000118" not null enable validate  );
alter table "DM"."TIMES" modify ( "CALENDAR_YEAR_ID" constraint "COAD_NN000119" not null enable validate  );
alter table "DM"."TIMES" modify ( "MONTH_ID" constraint "COAD_NN000121" not null enable validate  );
alter materialized view "DM"."CB$SALES_CUBE" compile;

Also you may wish to change the constraint type from enforced or trusted or norely to ‘RELY’ since they are cheaper for query execution.

e.g for the above statements, alter table "DM"."SALES_FACT" add constraint "COAD_PK000126" PRIMARY KEY (CHANNEL, DAY_KEY, PRODUCT, CUSTOMER) RELY enable validate ;
 

For continuing execution of Query Rewrite, ensure statistics have been collected for the Cost Based Optimizer for every dimension and the cube: e.g.
 

SQL> exec dbms_aw_stats.analyze('CHANNEL');

SQL> exec dbms_aw_stats.analyze('TIME');

SQL> exec dbms_aw_stats.analyze('PRODUCT');

SQL> exec dbms_aw_stats.analyze('CUSTOMER');

SQL> exec dbms_aw_stats.analyze('SALES_CUBE');

Refer here for a script to get a list of dimension and cube MVs

Back to Topic List

Refreshing the MV

As can be seen in the screenshot below, in the MV details tab, this MV is unusable. An MV needs to be USABLE to be used.

To do this, refresh the cube, You can refresh the MVs in the following ways:

Automatic Refresh: On the Materialized View tab for a cube, you can create a regular schedule for the materialized view refresh subsystem. There are 3 refresh modes available:

• On Demand: Waits for you to initiate a refresh
• Start/Next: You schedule the refreshes.
• On Commit; Initiated a fast refresh whenever the database commits a transaction that changes the source table.

This is further described in "Adding Materialized View Capability to a Cube" on page 3-24 of the OLAP User's Guide.

You can refresh as follows, where types 1 and 2 are recommended for cube MV Refresh:

1. Maintenance Wizard in AWM: The Maintenance Wizard is available for refreshing all cubes and dimensions, including cube materialized views.
    
2. DBMS_CUBE: The DBMS_CUBE PL/SQL package is available for refreshing of the AW objects; cubes and dimensions and the MVs and will use the Refresh Mode defined for the cube in AWM.

   SQL> execute dbms_cube.build('SALES_CUBE');

   If you have multiple cubes you want to update in the same procedure, you can do this:

3. SQL> execute dbms_cube.build('SALES_CUBE, COST_CUBE');

4. DBMS_MVIEW: The DBMS_MVIEW PL/SQL package contains several procedures for use with cube materialized views.

The methods that are available for dbms_mview.refresh are the same methods as listed previously.

You can check the status of all the MVs by viewing the output of :

SQL> col cube_mv hea 'Cube Materialized View'
SQL> col rewrite_enabled for a12 hea 'ReWriteable'
SQL> col staleness hea 'Staleness'
SQL>
SQL> select owner||'.'||mview_name cube_mv, rewrite_enabled, staleness
2 from all_mviews
3 where container_name like 'CB$%';

Cube Materialized View                                             ReWriteable              Staleness
----------------------------------------------------    ------------              --------------
DM.CB$TIME_CALENDAR                              N                     FRESH

DM.CB$PRODUCT_STANDARD                     N                     FRESH

DM.CB$GEOGRAPHY_REGIONAL                N                     FRESH

DM.CB$CHANNEL_SALES_CHANNEL        N                     FRESH

You can select from Data Dictionary object user_mviews to view jus the MVs in the user schema also.

If STALE, this means some data in the source has changed and you should refresh the MV, though its not essential since the MV is still USABLE.

Back to Topic List

SQL Execution Compared: 

1. Run Select statement without MVs/ Query Rewrite on (disable Query Rewrite):

select SUM (s.Sales) as Sales,
t. calendar_year_name,
p.department_name,
c.class_name,
cu.country_name
from times t,
products p,
channels c,
customers_v cu,
sales_fact s
where p.dimension_key=s.product
and s.day_key=t.day_key
and s.channel= c.dimension_key
and s.customer=cu.customer_key
group by p.department_name,
t.calendar_year_name,
c.class_name, cu.country_name

See explain plan/execution of this query below. This will show expensive joins and long query time:

2. Scenario after MV/ Query Rewrite enabled and query executed: simple select statement and quick response time against the MV. Execution against the MV is transparent to the user.

Back to Topic List

Troubleshooting Query Rewrite

To troubleshoot the performance of Query Rewrite and the use of Materialized Views, refer to MetaLink Note: 577293.1. In summary this note details that you should check the following:

• Query Rewrite Enabled and MV state OK.
• Database/Query Rewrite privileges correct - check user running query has GLOBAL_QUERY_REWRITE and rights to the appropriate objects
• Cube set up properly for rewrite - e.g. aggregation type, constraints, no complex calculations in the cube,
• Statistics - check that  statistics have been collected for the Cost Based Optimizer to perform well e.g.

  SQL> exec dbms_aw_stats.analyze('CHANNEL');

  SQL> exec dbms_aw_stats.analyze('TIME');

  SQL> exec dbms_aw_stats.analyze('PRODUCT');

  SQL> exec dbms_aw_stats.analyze('CUSTOMER');

  SQL> exec dbms_aw_stats.analyze('SALES_CUBE');

  Refer here for a script to get a list of dimension and cube MVs

More advanced Debugging

Set the following for ease of debugging:
 

• Alter session Set query_rewrite_enabled =FORCE will help with debugging since Query Rewrite should always be used if an MV is available.
• Alter session Set query_rewrite_integrity=stale_tolerated . MVS will be used even if stale.
• Use Explain Plan to see how the query is being executed. (Easy to use in SQL Developer). In SQL*Plus:
  • SQL> EXPLAIN PLAN FOR <your SQL query>
  • SQL> SELECT operation, options, object_name FROM plan_table; will show you operations
  • SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display()); will show you operations and their cost.
  • Refer to http://www.oracle.com/technology/obe/obe10gdb/bidw/mvplans/mvplans.htm  for more information on this.
• Use dbms_mview.explain_rewrite to see why Query_rewrite failed. Here is an example of how to do this:
• Run the query within the dbms_mview.explain_rewrite procedure:

  begin
  dbms_mview.explain_rewrite('select SUM (s.Sales) as Sales,
  t. calendar_year_name,
  p.department_name,
  c.class_name,
  cu.country_name
  from times t,
  products p,
  channels c,
  customers_v cu,
  sales_fact s
  where p.dimension_key=s.product
  and s.day_key=t.day_key
  and s.channel= c.dimension_key
  and s.customer=cu.customer_key
  group by p.department_name,
  t.calendar_year_name,
  c.class_name,
  cu.country_name','CB$SALES_CUBE','a3');
  end;
  /

Output will go to rewrite_table within the user's schema.

This example will show the following execution plan in SQL Developer when Query Rewrite is enabled and the environment is set properly:

The Query Rewrite 'MESSAGE' will look like:

If Query Rewrite does not work, running the query may invoke the following output:

You can see the following output in the message log, which you could then troubleshoot.

Back to Topic List

Final Notes

Refer to following notes for further assistance:

MetaLink Doc Id 577293.1: Oracle OLAP 11g: How to ensure use of MVs/Query Rewrite

MetaLink Doc Id 258252.1: MV Refresh, Locking. Performance, Monitoring

OBE: http://www.oracle.com/technology/obe/obe10gdb/bidw/mvplans/mvplans.htm

Oracle OLAP User's Guide 11g Part Number B28124-02

Back to Topic List

Summary

This note gives an overview of how to set up Cube MVs for Oracle OLAP cubes, and how to ensure that Query Rewrite leverages them.