Hi Tom,
There is a process of nightly Load every day which is scheduled so we have Materialized view (aggregation are there in MV base on some table) which is refreshed daily as a 'complete' refresh.so as the data grows daily so the performance of complete refresh is getting slower.
pr_fact_x has billions of records.
Please suggest how we can better do this or any other way we can achieve this.
code snippet of the MV:
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 ;
The query looks like a straightforward join + group by. So I'd look to see if you can make this FAST REFRESH (ON COMMIT)
Instead of re-running the whole query in the MV, this can apply the changes made since the last refresh. This can be significantly quicker than complete refresh - provided the number of changes is "small".
To do this, you'll need to create a materialized view log on each table first.
I discuss MVs further in this video:
If you can't get fast refresh working, use dbms_mview.explain_mview to find the issues
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9541146000346353098 If you're still struggling after this, post your findings here.