Materialized View Maintenance – Part I: Guidelines


I have been working with a lot of materialized views in our PeopleSoft Campus Solutions Warehouse lately and let me tell you, they can be a pain some times. Before I go into technical details here are some best practices that we have developed in our environment for data that is loaded once nightly:

  1. Do not use the ON COMMIT refresh method, it’s better to use REFRESH COMPLETE and  ON DEMAND or something like NEXT TRUNC(SYSDATE+1) + (1/24), this is generally applicable to most data warehouses with no real time data streams.
  2. If data warehouse users create contention for your MView refresh it is advisable to schedule these processes to occur while the database is unavailable to them, in our case that is during the maintenance window right after the warehouse comes online after backup.
  3. As with other physical database objects it is advisable to separate indices from the base data, the easiest way to do so is using a different oracle tablespace to host them.
  4. Make sure to create a BITMAP index for each surrogate key column in your view to leverage oracle star joins when query rewrite is enabled.

It is important to note that these materialized views are being used as helper tables to  determine the most recent surrogate key values for each dimension entity and the datasets are fairly small, we are testing partitioning some of this data but haven’t come up with an optimal partitioning method so that we can reap the benefits of partition elimination to the degree we would like.

Check out the Puget Sound Oracle User Group page for the pacific northwest for a deeper look at Materialized Views and DBMS_MVIEW.

Tags: , , , , , ,

Leave a Comment