Home » SQL & PL/SQL » SQL & PL/SQL » Replication, Streams, Merge or MVs?
Replication, Streams, Merge or MVs? [message #236364] Wed, 09 May 2007 14:02 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Seeking an opinion/suggestion Smile We have 2 databases - database A that is our local db and then database B that is our client's db. Database B is not optimized for performance - it is not indexed properly, etc. We, of course, have no say there and have no ability to optimize it. What we have essentially been doing is A) recreating the same tables in our database, indexing them properly, etc and copying the data via MERGE over a dblink. The MERGE happens like every hour or so. Hence, we do not have real time data since we "refresh" on hourly basis. B)Using materialized views for some things (but when these views refresh the data is not availble). Anyway, we feel that the time has come to come up with a better alternative.

Say we have 50 tables on the clients side that we would like to replicate. We create the same 50 tables on our local db and index them properly, run statitstics, etc. Client's tables would not have the same indexes. What would be the best way to have REAL time data (if somethig gets inserted, updated, deleted on clients's db) to capture these changes in our db? Both dbs are running Oracle 9i. We have looked into Streams and Replication. What works better in 9i? Are there any other alternatives? Any thoughts/opinions would be very appreciated!
Re: Replication, Streams, Merge or MVs? [message #236516 is a reply to message #236364] Thu, 10 May 2007 02:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Streams looks to be a good solution for what you want. I have been reading up on it recently for a 10g implementation, so I'm not sure about the 9i functionality. From what I've read, local-capture will give you damn-near real time replication.

Fast Refresh Materialized views (simple SELECT * FROM tab@link) would be OK too, but there would be a higher impact on the client's DB with the MV Logs. Also, you would unreasonably impact the client application if you made them ON COMMIT to achieve real-time.

Ross Leishman
Re: Replication, Streams, Merge or MVs? [message #236651 is a reply to message #236516] Thu, 10 May 2007 09:08 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
great! thank you!
Previous Topic: unable to rename column
Next Topic: Need Query to simulate ROW_NUMBER
Goto Forum:
  


Current Time: Tue Dec 06 12:30:38 CST 2016

Total time taken to generate the page: 0.08437 seconds