| what is most effecient way to move data [message #93019] |
Thu, 05 September 2002 08:17  |
khaled awad
Messages: 1
Registered: September 2002
|
Junior Member |
|
|
Hi,
I am currently developing an ETL strategy to move data from an OLTP oracle db to an oracle warehouse db. I have a situation where I need to join five tables, three of them are outer joint, and move the resulted data into the target warehouse db table. The size of data is relatively huge, around 10 milion records (1.5 GB). I have developed two ways to move data:
1. stored procedure, which opens a cursor and fetch data from the master table, and for each record fetched, process it, and then insert it into the target warehouse db. The logic works fine, however it's very very slow.
2. use direct sql insertion, i.e. "insert into Target WH tabe select * from SourceJointTables". This requires lots of space in both rollback segment & temporary tablespace, and if a new logic to massage data is needed, it's very hard to implement if not impossible.
How do you advise to do this task?
I realy much appreciate your efforts to answer my question, and thanks in advance again & again.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
