0

I wan to setup a database such that its data come from queries run on a remote database. Basically the source database is very slow and we have no control over it. So, I want to create a second database such that it would run the queries periodically on the source database, and saves the results into this second database. Thus our team would only have to deal with this second database which is simpler and much faster.

Now, my question is what is the best way to run the queries and then save the result into the second database. I can use Java to do this, but I think there are better, and more efficient, database specific way of doing this. Note that I don't want to just replicate tables, but result of queries.

Thanks, Alison

Improve this question
2
  • 2
    Do you need to be able to update the data on the "copy" database? If not, then that's exactly what materialized views (called "snapshots" in earlier versions) were made for.
    – user1822
    Commented Feb 3, 2012 at 22:31
  • Alison, your google term is ETL. I'm not saying you are alredy doing ETL, but it sure sounds that you are bound there.
    – kubanczyk
    Commented Feb 5, 2012 at 11:52

1 Answer 1

Reset to default
2

Depends how much data you need on the second database. Solutions are not to save query results on a second DB but duplicate data for querying on another DB. Basically the following technologies:

  • Oracle Streams: it is a replication from one database to another one. Can be done by table or by schema.

  • Oracle Active Dataguard: it is basically a standby DB open in ReadOnly for query. It is the standard oracle solution for the real time query. This requires a supplemental license

  • ETL: as suggested in the comment you can setup some ETL jobs using either an ETL tool or manually creating some stored procedure access data on remote DB. ETL is not, usually, a realtime process.

  • MATERIALIZED VIEV: if data and tables are a very small set you can create some MVs on your second DB that update periodically. Again this is not likely a realtime process and also may add load on primary DB.

In any case you need a sort of access to the primary DB. If you do not have any access apart the standard used you use for normal access through you application probably you can implement only ETL with a stored procedure or materialized view.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.