MATERIALIZED VIEW REFRESH FAST AGGREGATION + OPERATION => ORA-12015

Question

I have a table with an identifier column (code) and a value column(val).

I have a functional materialized view on oracle 11g :

    CREATE MATERIALIZED VIEW "MYVIEW"
     BUILD IMMEDIATE
     REFRESH fast ON demand
     WITH ROWID 
     AS      
      SELECT 
      code,   
      sum(val)
      FROM mytable
      GROUP BY code
;

But if i edit like this (just add "+10" to the sum column) :

CREATE MATERIALIZED VIEW "MYVIEW"
     BUILD IMMEDIATE
     REFRESH fast ON demand
     WITH ROWID 
     AS      
      SELECT 
      code,   
      sum(val) +10
      FROM mytable
      GROUP BY code
;

I have an error :

ORA-12015: cannot create a fast refresh materialized view from a complex query

Why ??

Have you analysed the view query to see why it isn't fast-refreshable? — Alex Poole
Not Capable of: REFRESH_FAST REFRESH_FAST_AFTER_INSERT aggregate function nested within an expression... — k4st0r42

k4st0r42 k4st0r42 · Accepted Answer · 2015-08-19T08:14:11

The only solution I found : do it with 2 materialized views... One for the aggregation One for the operation +10

MATERIALIZED VIEW REFRESH FAST AGGREGATION + OPERATION => ORA-12015

1 Answers