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.
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!