Best/Fastest Approach Insert Clob data from csv file to oracle database

How to Insert data in a file that contain a clob field to oracle database.

Read 开发者_开发技巧from file. Insert into oracle database

There might be some complications involved when clob data is present.

Thanks, Naveen

I'd generally suggest SQL*Loader or external tables depending on whether the file is (or can be) placed on the database server or whether your intention is to load the file from a client machine.

The specifics of the SQL*Loader control file will depend on the format of your file but the documentation has examples of loading CLOB data stored in a data file that should get you started.

When doing this for my last company, we wrote a C# program using stored procs and multiple threads. Throttling the threads up and down helped with the load on the database. we couldn't use Loader because of some configuration settings. Associative arrays helped as well.

In our case, we couldn't do some optimizations because the tables were live, but turning off indexes and rebuilding them after the load can increase performance. We saw issues in our RAC environment with each rac server fighting over the index file, partitioning and adjusting the indexes improved performance, but I'm not really an Oracle guru, so you might want to ask someone smarter on that.

I do know that we saw very poor performance with CLOB data, such that we changed one of our fields to be a compressed string that spanned several varchar(4000) and then had the program stitch them back together when being used.

Minimise bottlenecks.

• Move the files so they are directly accessible to the DB server on a nice thick pipe.
• You want RAID 10 (not RAID 5) for your database datafile.
• Make sure your redo log files are on a different spindle from your data files. You don't want them fighting over the disk. You may want to turn off any multiplexing of redo log files for this. You (probably) don't want that as a permanent fixture on a production DB, but it is worth doing if this is a one-off job.
• Turning off archivelog mode may be worthwhile, as might be disabling any replication. Again, probably only relevant if this is a one off.

Once you've got your hardware geared for a heavy write load, then use SQL*Loader / external tables.

I doubt whether multiple threads / processes would help much as I'd expect the speed to be limited by the ability to write to disk(s) rather than CPU. If you can spread the LOBs onto a tablespace with files on multiple spindles, that could help.