What is the fastest way to insert data into an Oracle table?

Question

I am writing a data conversion in PL/SQL that processes data and loads it into a table. According to the PL/SQL Profiler, one of the slowest parts of the conversion is the actual insert into the target table. The table has a single index.

To prepare the data for load, I populate a variable using the rowtype of the table, then insert it into the table like this:

insert into mytable values r_myRow;

It seems that I could gain performance by doing the following:

• Turn logging off during the insert
• Insert multiple records at once

Are these methods advisable? If so, what is the syntax?

Answer 1

Drop the index, then insert the rows, then re-create the index.

Answer 2

It's much better to insert a few hundred rows at a time, using PL/SQL tables and FORALL to bind into insert statement. For details on this see here.

Also be careful with how you construct the PL/SQL tables. If at all possible, prefer to instead do all your transforms directly in SQL using "INSERT INTO t1 SELECT ..." as doing row-by-row operations in PL/SQL will still be slower than SQL.

In either case, you can also use direct-path inserts by using INSERT /*+APPEND*/, which basically bypasses the DB cache and directly allocates and writes new blocks to data files. This can also reduce the amount of logging, depending on how you use it. This also has some implications, so please read the fine manual first.

Finally, if you are truncating and rebuilding the table it may be worthwhile to first drop (or mark unusable) and later rebuild indexes.