I have 40 views in an Oracle 18c GIS database that are used in a map in a workorder management system (WMS).
- The views are served up to the WMS map via a web service/REST.
- The views have an average of 10,000 rows per view.
The views have joins to dblink-tables in a separate Oracle database, and as a result, are not fast enough for use in the WMS map (3-second map refresh delay). Furthermore, it seems like a bad idea to compute the views each time a user refreshes the map; since the map does not need to be up-to-date in real-time (an unnecessary burden on the DB).
As an alternative, I would like to take snapshots of the views on a weekly basis. The snapshots would be static tables that would perform much better in the WMS map.
Unfortunately, due to office politics challenges, using technology like materialized views or Oracle’s Golden Gate to solve this problem is not an option.
What are my options for taking scheduled snapshots of Oracle views (without using materialized views or Golden Gate)?
For example, I could make an .SQL script that truncates static tables and inserts the rows from the views into the tables (on some sort of schedule). But as a novice, I don’t know how efficient or risky that option would be, or if there are better alternatives.