Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 06, 2021 - 5:11 pm UTC

Last updated: January 08, 2021 - 5:11 pm UTC

Version: 19.3

Viewed 1000+ times

You Asked

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.

Thanks.



and Chris said...

You can create materialized views on partitioned tables and partition the MV too.

This is most effective when the database is able to use partition-change tracking. This enables it to track freshness at the partition level, rather than the entire MV.

You must meet a number of requirements for this to work though. I suggest reading up on this in the docs and coming back to us if you have specific questions:

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/advanced-materialized-views.html#GUID-43FA865F-8C02-4B99-9202-49728BAA7687

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.