We have around 20 Aggregate tables in our EDW and planning to convert all these into materiazed views.
Currently all the AGG tables are partitioned(range).Some of the base tables which form the materialized view definition are partitioned on different key and some are not even partitioned.
Considering that the the new MVs being created would be range partitioned just same as the corresponding existing AGG table, please advise what precautions I need to take before creating the MV views in my case.
Below are the parameters I am planning to provide for MV definition:
1. CREATE MATERIALIZED VIEW LOG on all base tables with all columns mentioned in the AGG table DDL- WITH SEQUENCE, ROWID, INCLUDING NEW VALUES
2. CREATE MATERIALIZED VIEW with partition key same as existing AGG table( irrespective of whether the base tables are partitioned/ not partitioned)
3. Other Parameters for MV : BUILD IMMEDIATE, REFRESH FORCE ON COMMIT, ENABLE QUERY REWRITE
Please advise if I am missing any thing here especially to avoid errors during MV refresh caused by PMOP on base tables.
and we said...