Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, manisha.

Asked: August 12, 2020 - 7:13 pm UTC

Answered by: Chris Saxon - Last updated: August 13, 2020 - 1:28 pm UTC

Category: PL/SQL - Version: 11g/12c

Viewed 100+ times

You Asked

hi tom,

This is with reference to question asked earlier about MV. i don't know how to write the revert against the questions asked.

https://asktom.oracle.com/pls/apex/asktom.search?tag=materialized-view-complete-refresh-getting-slower

Actually in below pr_fact_x is a view and day_pr_mst_x is again a MV which is rebuild means complete refresh every day so fast refresh is not possible.

create materialized view pr_month nologging
tablespace idm_msta_ts
using index tablespace idm_msta_is
as
   select -- + index_ffs(d) use_hash(d f)
         f.client_id,
         f.account_dim_id,
         f.org_dim_dc_id,
         f.org_dim_unit_id,
         f.instill_product_dim_id,
         f.instill_product_id,
         f.client_product_dim_id,
         d.month_period_num,
         sum(f.fact_delivered_cost) as TOTAL_COST,
         sum(f.fact_delivered_cases) as TOTAL_CASES ,
         sum(f.fact_delivered_weight) as TOTAL_WEIGHT,
         sum(f.fact_delivered_volume)  as TOTAL_VOLUME,
         sum(f.fact_delivered_count) as TOTAL_COUNT,
         f.currency_dim_id as CURRENCY_DIM_ID
  from   pr_fact_x f,
         day_pr_mst_x d
  where  f.client_id            = d.client_id
  and    f.invoice_trx_date = d.calendar_date
  group by f.client_id,
         f.account_dim_id,
         f.org_dim_dc_id,
         f.org_dim_unit_id,
         f.instill_product_dim_id,
         f.instill_product_id,
         f.client_product_dim_id,
         d.month_period_num ,
         f.currency_dim_id  ;


please suggest.

and we said...

Actually in below pr_fact_x is a view and day_pr_mst_x is again a MV which is rebuild means complete refresh every day so fast refresh is not possible.

You can make an MV which queries a view and another MV fast refresh on commit. Provided you create materialized view logs on:

- All the tables in the view
- The MV you query (day_pr_mst_x)

In theory this is doable. It's possible the nature of the view's query means fast refresh is impossible, but to answer that I'd need to see the view definition!

Similarly, if you're looking for help optimizing the query for complete refresh, I'd need so see the execution plan, which you get by running:

set serveroutput off
select /*+ gather_plan_statistics */ from <mv query>
select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


https://blogs.oracle.com/sql/how-to-create-an-execution-plan

So either way we need more detail here to help you!

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.