Hello Team,
The materialzed view below contains several tables.
create MATERIALIZED VIEW BI_GL_BALANCES
BUILD IMMEDIATE
REFRESH FAST AS
SELECT SYSDATE REFRESH_DATE,
dm_glbalances.budget_version,
dm_glbalances.company,
dm_glbalances.costcentre,
dm_glbalances.ACCOUNT,
dm_glbalances.subaccount,
dm_glbalances.project,
dm_glbalances.LOB,
dm_glbalances.partner,
dm_glbalances.intercompany,
dm_flex_company.description company_description,
dm_glbalances.flow,
dm_glbalances.spare,
dm_glbalances.actual_flag,
dm_glbalances.currency_code,
dm_glbalances.account_type,
dm_glbalances.period_name,
dm_glbalances.period_num,
dm_glbalances.period_year,
dm_glbalances.summary_flag,
dm_flex_costcentre.description costcentre_description,
dm_flex_account.description account_description,
dm_flex_subaccount.description subaccount_description,
dm_flex_project.description project_description,
dm_flex_partner.description partner_description,
dm_flex_lob.description lob_description,
dm_flex_intercompany.description intercompany_description,
dm_flex_flow.description flow_description,
NVL (dm_glbalances.beginbalance, 0) beginbalance,
NVL (dm_glbalances.ptdbalance, 0) ptdbalance,
NVL (dm_glbalances.ytdbalance, 0) ytdbalance
FROM dm_flex_flow,
dm_flex_company,
dm_flex_intercompany,
dm_flex_lob,
dm_flex_partner,
dm_flex_costcentre,
dm_flex_project,
dm_flex_account,
dm_flex_subaccount,
bi_gl_balances_v dm_glbalances
WHERE (dm_flex_company.flex_value = dm_glbalances.company)
AND (dm_flex_account.flex_value = dm_glbalances.ACCOUNT)
AND (dm_flex_project.flex_value = dm_glbalances.project)
AND (dm_flex_costcentre.flex_value = dm_glbalances.costcentre)
AND (dm_glbalances.LOB = dm_flex_lob.flex_value)
AND (dm_flex_partner.flex_value = dm_glbalances.partner)
AND (dm_flex_subaccount.flex_value = dm_glbalances.subaccount)
AND (dm_glbalances.intercompany = dm_flex_intercompany.flex_value)
AND (dm_flex_flow.flex_value = dm_glbalances.flow);
Materialized view logs creaated as shown below:
CREATE MATERIALIZED VIEW LOG ON m_flex_flow with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_company with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_intercompany with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_lob with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_partner with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_costcentre with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_project with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_account with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dm_flex_subaccount with rowid,PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON bi_gl_balances_v with rowid,PRIMARY KEY;
Kindly advise why I cannot do fast refresh?
Thanks,
Roshan