Possible Duplicate:
Oracle - How to create a materialized view with FAST REFRESH and JOINS
I have one mv UTIL_MONTH and table MET_MONTH both table have 1 million rows per month. I am getting
ORA-12015: cannot create a fast refresh materialized view from a complex query.
This is the structure of UTIL_MONTH (materialized View)
year
month
customer_id
att1
att2
att3
and of MET_MONTH (Table)
year
month
customer_id
amt_12wk
amt_24wk
amt_36wk
This is the code to create the materialized view with a fast refresh.
create materialized view monthly_mv
parallel
build immediate
refresh fast on demand
enable query rewrite
select
t.year,
t.month,
t.customer_id,
t.att1,
t.att2,
t.att3,
u.amt_12wk,
u.amt_24wk,
u.amt_36wk,
from util_month t
join met_month u
on (t.customer_id = u.customer_id
and t.month = u.month
and t.year=u.year)
This is very simple join and I don't want to go for a complete refresh. Is there any workaround for this?