What are the approaches for refreshing materialized views in Oracle, when underlying tables are updated often

materialized-vieworacle-12c

I am a web developer, maintaining a web app that tracks orders, customers, products, etc, that my client uses internally. I use Oracle 12c, hosted on AWS RDS. My client has just switched some other systems, so we are at a point where data structures have changed, and I am using a new schema in Oracle to store new data in the new structures.

In order that the web app doesn't have to be re-engineered to work with new data structures, a decision was made to implement materialized views in Oracle that union the new data from the new schema (manipulated into the "legacy structure") together with the legacy data.

So now I have to deal with refreshing these materialized views so that the web app constantly has access to the latest data. Ideally the relevant materialized view(s) would be refreshed whenever I receive a new record into the new schema, but during working hours, I receive new data every few seconds maybe. A compromise is OK – if the materialized views are stale by a few minutes (maybe 5 or (less ideally) 10 minutes), that might be an acceptable situation.

My question is, what approach should I have for refreshing these materialized views? I don't want to overload Oracle with constant refreshes, and the web app should provide users with a good user experience when reading/writing data from/to Oracle. I'm far from being an Oracle/DB expert, so I am not really sure what options there are. I guess I could just have a cron job that runs every 5 minutes or something to refresh stale materialized views one by one, but I am wondering if this approach is a bit naive.

In reality, I am dealing with 14 materialized views (for now), and in my testing, some of them take up to 2.5 minutes to do a complete refresh.

Best Answer

If you can, use fast refresh instead of complete refresh - it will save you a lot of compute time. you'll have to create materialized view logs on the source tables, but if you run the refreshes frequently you will keep them small and only have to deal with the incremental updates. If you're using 12.2 you can also use "real-time" materialized views, as illustrated here: https://www.youtube.com/watch?v=k5nhZfyPHdY