2

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?

Community
  • 1
  • 1

0 Answers0