Home » Developer & Programmer » Data Integration » what is most effecient way to move data
what is most effecient way to move data [message #93019] Thu, 05 September 2002 08:17 Go to next message
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.
Re: what is most effecient way to move data [message #93020 is a reply to message #93019] Fri, 06 September 2002 04:43 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
Hi,

In your case I would use the transportable tablespace option which is available in Oracle8i. With this feature you can copie the datafiles of the tablespaces used by your tables of your source database to your target database (of course only if the 2 DBs are on the same OS). For more details see:
http://doc.oracle.com/cd_a87860/doc/server.817/a76994/transpor.htm#12012

Once your tablespaces has been copied to your DWH DB you can insert your data in your tager table by using the append and nologging options. For more info see:
http://doc.oracle.com/cd_a87860/doc/server.817/a76994/tuningpe.htm#51909

HTH
Mike
Previous Topic: OSA/EXPRESS
Next Topic: create OLAP metadata manually
Goto Forum:
  


Current Time: Fri Mar 29 04:34:14 CDT 2024