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.
