Posted by: Peter Scott | August 8, 2006

Redefining a M-VIEW query

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.

About these ads


  1. Maybe we need to form a pressure group — Oracle Professionals Against Materialized Views Without Prebuilt Tables (OPAMVWPT). It just rolls off the tongue!

    Nice post, and *amen* on the 10g — my client is upgrading later this year.

  2. After the BO upgrade? – one of my customers is stuck on 9.2 until he can upgrade from BO 6.1….

  3. [...] may give advantage, suggests Pete-s random notes. Pete explains his approach to tables for M-VIEW queries in [...]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



Get every new post delivered to your Inbox.

%d bloggers like this: