- The views are served up to the WMS map via a web service.
- There are 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.
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 well in the WMS map.
Unfortunately, due to office politics issues, 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 (although, as a novice, I don't know how efficient or risky that option would be, or if there are better alternatives).