Database DataWarehousing (MOSC)

MOSC Banner

materialized view with refresh on commit

edited Mar 11, 2011 6:00AM in Database DataWarehousing (MOSC) 2 commentsAnswered
1. I have a view:

     create view ni_v as select * from cartis union select * from ni_c;

2.  i try to create materialized view

create materialized view ni_mv
refresh on commit
as
    select account
    ,sum(mataz)                    execution_mtz
     from ni_v
     where account between '200000' and '299999' -- to quick the query
     group by account

3. I receive  

     ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

4. i built the above mv but with BUILD DEFERRED

5. result  of EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('ni_mv') is :

     MV with UNION ALL in a view is too complex

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center