DBMS_ADVISOR.TUNE_MVIEW

Oracle9i came with two packages, DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a materialized view wasn't being used for query rewrite. However, although these packages told you why rewrite hadn't happened, they left it down to you to work out how to alter your CREATE MATERIALIZED VIEW statement to ensure that rewrite happened correctly. Oracle 10g’s DBMS_ADVISOR.TUNE_MVIEW builds on this functionality and tells the DBA what changes to make to a materialized view to make it suitable for query rewrite, taking as its input a CREATE MATERIALIZED VIEW statement and outputting a corrected version that supports query rewrite and features such as fast refresh.

Tuning a materialized view follows a similar process to that used with the SQL Access Advisor.

1. First, create a task and supply a CREATE MATERIALIZED VIEW statement to DBMS_ADVISOR.TUNE_MVIEW

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(‘my_first_mv’, ' -
CREATE MATERIALIZED VIEW sales_mv -
REFRESH FAST -
disable QUERY REWRITE AS - 
  select   category, country, sum(sales) -
  from 	   product p, geography g, sales s -
  where    s.product_id = p.product_id -
  and 	   s.geography_id = p.geography_id -
  group by p.category, g.country
');

2. You can then directly query the resulting tuned CREATE MATERIALIZED VIEW statement from the USER_TUNE_MVIEW view:

SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME= ‘my_first_mv’ AND
SCRIPT_TYPE='IMPLEMENTATION';

3. Or you can output the tuned statement to the filesystem using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure.

SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME= ‘my_first_mv’ AND
SCRIPT_TYPE='IMPLEMENTATION';

The DBMS_ADVISOR.TUNE_MVIEW procedure has the capability to add additional aggregate columns and materialized view logs to the view definition so that it becomes fast refreshable, and it can restate the view definition (sometimes breaking it into separate, simpler definitions) so that it is more likely to satisfy query rewrite restrictions.

DBA Advisor Views:

dba_advisor_journal
dba_advisor_objects
dba_advisor_parameters
dba_advisor_rationale
dba_advisor_recommendations
dba_advisor_sqlw_journal
dba_advisor_tasks
dba_advisor_usage