2

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.
  • 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.

The Catch:

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).

  • implement your solution at least for one table an check if there are any problems. If there are problems ask again. I think inserting 40*10000 rows is not a big thing. what is the advantage/drawback of using truncate instead of delete? – miracle173 Jan 1 at 22:23
  • I'm not an Oracle DBA, so I can only provide advise for PostgreSQL, MySQL, and SQL Server. But, your problem follows a pattern here where you say "40 views", and then speak vaguely. We can't solve that. We can only address technical problems. Focus on one of those views, provide the query behind the view, and provide the query plan and the like. Even if your whole DB is slow, there isn't a magical "make fast" button. Hopefully you can learn by addressing one instance of your problem, but regardless this site doesn't address vague generalities that well. – Evan Carroll Jan 2 at 15:08
  • If by office politics you truly mean monetary constraints, please state that. I fear the term office politics will improperly skew answers as it implies you can afford more elegant solutions. – John Eisbrener Jan 3 at 16:18
2

You could create a function-based index on the sdo_geometry column in this view. This might help the query speed.

If you can't create an MV (have you tried?), create a table (create table as select...), then write a procedure that executes a MERGE from the view in to the table, then schedule it to execute every X minutes using dbms_job or dbms_scheduler.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.