1 Reply Latest reply on Aug 22, 2013 4:13 AM by sagargole

    Materialized View Refresh Methods


      I have a master table say PRODUCTS (non partitioned) having 4 million records and a partitioned (range on YYYYMM) SALES table in 10g database.

      There is monthly data load into the SALES tables, not too much data change on PRODUCTS table though.

      I need a materialized view on Products Sales; what should be the best approach to create and refresh the materialized view.


      So far I have created Materialized View mv_products_sales on prebuilt table and refresh on demand. The prebuilt table mv_products_sales is range partitioned on YYYYMM and created nicely.

      Please suggest if it is right approach and what should be the refresh method.

        • 1. Re: Materialized View Refresh Methods



          Building the materialized views on a prebuilt table has its own advantages. I would recommend the following articles for more details on materialized views on Prebuilt tables.


          Hemant's Oracle DBA Blog: Materialized View on Prebuilt Table



          As far as refresh methods go you have done the right thing by specifying the REFRESH on DEMAND clause. Here are a few points you need to consider.


          • Since data is going to be incrementally loaded in SALES table refreshing your materialized view with the 'C' (Complete) clause every time is going to be a costly affair.
          • It is recommended that your materialized view be eligible for FAST refresh on your PRODUCTS table and PCT and FAST refresh on your SALES table. You can use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to know the capabilities of your materialized view. You can check this link for more details DBMS_MVIEW.
          • Since data is loaded at a monthly frequency in your SALES table it would be ideal that you refresh your materialized view using the 'P' (PCT) clause. However, this kind of refresh may fail if there are any inserts or updates in your PRODUCT table since the PRODUCT table is not partitioned and the materialized view will not be eligible for PCT refresh on the PRODUCT table. In such cases it is advisable to refresh the materialized view with the F (FAST) option.


          Please let me know if you require any additional details.