Hi,
I am trying to load nearly 10 million of data into a Oracle table (Trunc and load) using the ODBC stage from a CSV file. It's really taking long time like 1200 rows/sec.
I can't use the Oracle Enterprise stage for doing trucate and load, because in my company we dont have access to drop and create the indexes as Oracle drops the indexes and recreates them if we use the trunc and load.
my job sequece is:
Sequencial file stage -> column generator -> transformer -> DB
i am using the column generator to create the surrogate key.
Can you please advice me how can i improve the performance?
Loading data into Oracle DB is slow
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue May 11, 2010 2:05 pm
The correct and best solution would be loading the data into some temporary stage tables first. Then using ODBC/Oracle stage run a PL/SQL procedure which dumps the data from stage tables to Actual tables. This would be pretty fast. I have worked with 60 millions records using the same concept but with the Ab Initio tool.
vdlrao
-
- Participant
- Posts: 30
- Joined: Tue May 11, 2010 2:05 pm
-
- Participant
- Posts: 30
- Joined: Tue May 11, 2010 2:05 pm
Its not because of the duplicate values but because DBA didnt grant privilage to rebuilt the indexes. Now i have the privilage to rebuilt the indexes and working fine.
Only my concern is its taking a while to re-built the index after loading the data. Can some one tell me how the indexes are rebuilt and what goes on behind the scene ?
Only my concern is its taking a while to re-built the index after loading the data. Can some one tell me how the indexes are rebuilt and what goes on behind the scene ?