• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Moving data across 2 database servers periodically

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Forum,

I am working on a JAVA e-commerce application with Oracle 9i database. All the transactions that take place are logged in Table-A on Server-A. The requirement is that all the transactions that more than 60 days old should be moved to Table-B on Server-B. The solution should be executed everyday at fixed time.

1: Structure of table A and B is identical and is with no partitions.
2: Server A and B are two different machine running identical Oracle Database.
3: The data that may be moved from A to B will be in thousands of records.

Please suggest an efficient solution.

Thank you,
Nitin
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This does not seem to be that complicated. Thousands of records should not pose any significant problems, it's tiny amount of data by today's standards.

If you do the obvious and use a Java application to select data from Server-A and insert them on Server-B, make sure that you're using Oracle's own update batching mechanism, or use Oracle 11g driver, which provides efficient mechanism even for JDBC standard update batching. Oracle JDBC drivers older than 11g do not provide the topmost performance with JDBC update batching, and this can be significant. We've got a few notes on driver compatibility and update batching in our Oracle FAQ.

If you want to improve the performance further anyway, I can see two clear possibilities:

1) Create a database link between the two databases and copy the data using a single command INSERT INTO AS SELECT (possibly using /*+ APPEND */ hint, but learn the ramifications first - if you delete data from Table-B, this approach would never reuse space freed by deleting the old data). This makes the data to travel over network just once; if you copy them using Java on an application server, the data would travel over the network twice. Check the documentation for limits and restriction associated with a link, I believe that either LOBs or LONGs could pose some problems.

You probably also could code a stored Java procedure on one of the databases, which would connect to the other database and copy the data by SELECTing and then INSERTing them. This would obviously eliminate one of the two network journeys for the data. I believe this (that is connection to another server from java stored procedure) should be possible, but have no experience on that. Also, I've moved from 9i long ago.

2) Partition the Table-A according to the transaction date and erase old data using ALTER TABLE DROP PARTITION, which is instantaneous and does not generate undo/redo for the data. You'll have to set up a process to add new partitions on the fly, but this all could be part of the process to move the data. This may significantly decrease load and undo/redo space utilization on the Server-A.
 
N Jain
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Martin,

Thank you for the response.

The application makes use of EJB 3.0 and JPA for data access and persistence. I had thought of implementing a solution using JPA; however on doing further study realized that it may not be an efficient way for archiving and then purging huge data.

I will first try and implement a solution using JDBC Update Batching first and then try out database solutions.

Best Regards,
Nitin
 
reply
    Bookmark Topic Watch Topic
  • New Topic