Maintenance Operations Required for Materialized Views

[ggnanaraj]

We see some performance benefit in using Materialized Views in our OLTP environment. We are using MV logs and ROWID MVs - in the absence of explicitly defined primary keys on the base tables - understand that it is advisable not to use ROWID MVs.

Looking out for information about the maintenance activities that need to be carried out for MVs - yet to find the same in the docs. In the meantime, I thought I could raise it up here so that those who are using MVs in their environment can share their experience on the same.

Thanks in advance.

[PAVB]

In my experience Materialized Views are great for Data Warehousing because of query rewrite feature, they are also pretty good to solve some simple replication requirements yet I'm still waiting to see how they could help in a properly designed OLTP environment.

[ggnanaraj]

It is providing performance benefit during "batch processing", where about 5 years history data is being loaded from multiple tables.

[PAVB]

It is providing performance benefit during "batch processing", where about 5 years history data is being loaded from multiple tables.

ETL? That's a very unusual operation to do in a system defined as being OLTP.

[gtcol]

We too have defined MVs in our OLTP, with refersh on DMLs; we hardly have few hundreds of transactions a day. This is coming out to be a major bottleneck for our application performance.