Data Warehousing

Redefining a M-VIEW query

Peter Scott

Aug 8, 2006 • 1 min read

Occasionally, people ask me why I prefer to use pre-built tables for materialized views rather than use the syntax that creates the view and allocates its storage. One reason I give is that it gives me the option of changing the MV query to improve the chances of rewrite. And how likely is that they ask?

One of our customers uses MVs and query rewrite extensively with their Business Objects reported DW. For sales they only needed to expose two views to the query tool and rewrite looks after the rest. One of the views is the 'straight forward' no aggregation on any dimension view and the other is half way up the customer dimension but with some extra derived columns to support some fancy analysis. Like others we use our own code to refresh MVs on a partition-by-partition basis.

Yesterday, we noticed a user query running for a long time against the base SALES summary when the select statement indicated it would be better served by the special summary - it matched exactly on the key columns. But rewrite to this was not possible because of the extra columns in the view definition. As we did not use the view definition to build the MV we decided to redefine it by

Drop MVIEW
Alter the prebuilt table to have default values for the columns that are not 'maintained' in our new view definition - this step is important!
Recreate the mv on the prebuilt table as never rebuild and using the new query

Result: the MV can now also be accessed by rewriten queries against base sales. And I reduced to 5% the run time of a horrible on the fly aggregation.

Of course if this was 10g I could cheat and use rewrite equivalence.

Sign up for more like this.