Home » Developer & Programmer » Data Integration » Daily data population
Daily data population [message #93162] Mon, 12 May 2003 07:25 Go to next message
viv
Messages: 11
Registered: February 2003
Junior Member
Hi,

I have a daily job that populates several tables in database A with updated data from a few remote databases(B,C etc.). It's not straight copies but with some logic. The tables are cleared every time and populated with new data. It is done throw procedures and db links. Due to the volume, the job runs quite some time. And I have been experiencing various errors (snap shot too old etc.) recently as data grows. Because of the dependence between tables, fail to populate one table will cause the data intact to be broken.

I was wonder are there alternatives that can handle this better. The most important is to keep data intact and efficiency is a big help too. I am thinking of creating temporary tables in database A to receive the updated data. If all the temporary tables are populated correctly, move the data to the actual tables.

Your suggestions are appreciated and thanks in advance.
Re: Daily data population [message #93163 is a reply to message #93162] Mon, 12 May 2003 08:43 Go to previous messageGo to next message
velappan
Messages: 2
Registered: May 2003
Junior Member
Try materialized views with the FAST option. you should be fine. good luck!
Re: Daily data population [message #93165 is a reply to message #93163] Tue, 13 May 2003 06:57 Go to previous messageGo to next message
viv
Messages: 11
Registered: February 2003
Junior Member
Thanks. Can I control the order of the refresh of the views? Since there are dependencies between tables.
Re: Daily data population [message #93203 is a reply to message #93162] Thu, 25 September 2003 13:24 Go to previous message
Manish
Messages: 79
Registered: December 2000
Member
First thing i would do try and avoid is doing the transformation across a dblinl.
1) create materialized views on your database( where you load the data) with the same structure as that of the tables in the other database.
2) use fast refresh to populate the materialized views. You can also index the views.
3) to eliminate snapshot too old. use large rollback segments.
please refer Oracle documentation on materialized views for more details.

hth
Manish
Previous Topic: ASample project
Next Topic: Time to take complete Refresh MATERIALIZED VIEW
Goto Forum:
  


Current Time: Thu Apr 02 05:11:51 CDT 2020