| Copy data's from Transaction database(db1) to Reporting database (db2) [message #483978] |
Fri, 26 November 2010 02:51  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi,
We are copying our transaction tables data into another database for our reporting applications
(say every day midnight refresh will happen).
The Transaction Database has some 30tables.
Existing system is following below points and it is taking 2hours to complete.
1) Truncate data from reporting database (or schema)
2) Direct path Insert into reporting database (or schema) as select * from transaction tables.
3) Rebuild index and Enable constraints.
Note: Each tables data will vary from 30lakhs to 50lakhs.
Dump/import/export is not advised by the client.
I want to cut down the time i.e., below 2hours.
Instead of above method. Can go for a field in each table specifying the time
of each records update/insert operation and then pick the modified records only and
copy into reporting db.
Can I have some comments on this approach ... like merits, demerits,complications ...
Thanks in Advance,
Ranjan.
|
|
|
|
|
|
|
|
| Re: Copy data's from Transaction database(db1) to Reporting database (db2) [message #483989 is a reply to message #483982] |
Fri, 26 November 2010 03:55  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
sorry about the type error(lakh), I will make sure this will not repeat.
In reporting db, we are inserting data into some 15 another tables(apart from tables in transactional database) and insert is happening after some business logic on data.
Now, my point of concern(scope)is only on these 15tables. Above mentioned 3 steps are being
used to insert data into these 15tables.
Can I have some suggestions on how to improve performance.
can you please elaborate "Streaming".
-Ranjan
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
