Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Feb 5 '12 at 1:20

This question came from our site for professional and enthusiast programmers.

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. –  a_horse_with_no_name Feb 3 '12 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 Feb 5 '12 at 11:52

1 Answer 1

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 techonologies:

  • 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 store proc 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 storeproc or Materialized view.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.