Loading data into Oracle DB is slow

[pradeep9081]

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?

[ArndW]

ODBC to Oracle is slow. Why not use the enterprise stage, but don't do a LOAD - that should be faster. The correct solution is to get access so that the fast bulk load can be used.

[nvdlrao]

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.

[pradeep9081]

Thanks andrew.

DBA granted the privilage to DROP and CREATE indexs. I am using the enterprise stage now with LOAD. i set the poperty Index Mode= Rebuild in options.

Now i am getting the error Index on table has not been rebuilt and Index status is UNUSABLE.

[chulett]

Typically that's because it is a unique index and you've loaded duplicate values into it. Ask your DBA for help if you are unsure what is going on but the reason should be in the .log file.

[pradeep9081]

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 ?

[chulett]

Your DBA can.