Our production database includes a table that is at about 100 million records and will likely get to a billion records before too long. Every other table is small--less than a million records.

The large table depends on the small tables. Individual records are calculated from the small tables and can only be properly interpreted using the context the small tables provide.

We want copies of the small tables for use in staging and testing. We also want them for interpretation of archived versions of the large table. It would be easy to get and maintain these copies if the large table weren't in the production db, so it's tempting to move that table somewhere else. However, we want the systems that interact with the large table to have up-to-date information on the small tables.

Is there a nice way to move the large table out of the production database while maintaining its access to updates on the small tables that are in the production database?

  • You could potentially move it to a different database in the same cluster and set up a database link.. Not really sure if this is a good idea, though. If the end goal is to be able to dump the database to restore into different environments (like a dev environment), wouldn't it be easiest to dump the whole db and then drop the large table from the new environment? – Joishi Bodio Oct 26 '16 at 21:14
  • I think you can add update_dt (timestamp) column to record the last change on each rows. Next, you copy full data of your table at 1st, then incremental data at 2nd and etc (if it's necessary). In terms of incremental data (compare with update_dt to collect newest update), you can use Symmetricds, Database Link, or trigger tool to capture newest data on your production then transfer to your development environment. But the problem in here, you need a primary_key to define rows which are updated. My solution likes ETL data in data warehouse. – Luan Huynh Oct 27 '16 at 2:55

Your Answer

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

Browse other questions tagged or ask your own question.