But Wait, What is SQL*Loader? And Why? I Already Have SQL Developer!

SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command or use the Enterprise Manager interface.

SQL*Loader is an integral feature of Oracle databases and is available in all configurations.

Now you may think it’s weird to tell someone not to use SQL Developer. But it’s not – I’m helping our user find the best way using our tools to accomplish their goal – load 1,000,000 rows faster. And, they can still use SQL Developer to get started.

My ‘friend’ on StackOverflow didn’t seem to believe me that it was faster, so I was asked to PROVE IT. Here we go –

Creating the Table

CREATETABLE"HR"."T_MILLION"("C1"NUMBER(*,0),"C2"NUMBER(*,0),"C3"NUMBER(*,0),"C4"NUMBER(*,0),"C5"NUMBER(*,0),"C6"NUMBER(*,0));
-- now put a million rows in it BEGINFOR i IN1..1000000 loop
INSERTINTO t_million VALUES(1,2,3,4,5,6);
END loop;
commit;
END;
/

I ran this a couple of times. First run time was 22.842 seconds, and the second run was 29.858 seconds.

Oh, and let me describe my setup:

Windows 10, running VirtualBox Linux Image with 12cR2 db, using SQL Developer 18.3 on my Windows host, connecting to the db over a VBox networking port forward. And I was doing video conference AND installing Oracle 12cR2 client during my testing. So my machine was having LOTS of fun today.

Running INSERTs, row by row – in SQL Developer

I queried my table, used the SQL Developer EXPORT feature to export to an INSERT script, with no COMMITs.

And then I ran it.

And I ran it for 17 minutes and 12 seconds before getting bored and cancelling the script. So that got me 353,233 records. Which tells me we were inserting about 342 records a second.

The SQL*Loader Solution

I truncated my table, and populated it again with my PL/SQL block above. Then I queried my table again and I created the SQL*Loader scenario:

Note when doing this type of export, we write our 2 different files, so you MUST select a directory to write to.

Disclaimer: I suck at maths, so my numbers might be off, but the results are pretty clear.
Disclaimer: I did NO enhancements or tricks on either side – this was running everything at the defaults. I’m sure I could get the SQL*Loader scenario to run EVEN faster.

So we went from 1 minutes, 33 seconds to less than 9 seconds. That’s 111,111 rows per second. And this is on my crappy desktop machine connecting to a DB running in a very small Linux VM. So in real world, expect even better.

Nothing is free by they way, the read buffer goes way up…but I’ll write that check.

Pretty much, if you look at the docs –
A bind array is an area in memory where SQL*Loader stores data that is to be loaded. When the bind array is full, the data is transmitted to the database. The bind array size is controlled by the BINDSIZE and READSIZE parameters.

The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS.

So fewer back and forths from client to server…sounds like a great question for AskTom.

OK I am probably being stupid and realize this question is slightly off the point of your post but why is the insert in the PL/SQL block only taking 22 seconds and the script with individual inserts but *no commit* taking so much longer?

The main thing that masks the PL/SQL block so much faster is that it’s doing a bulk insert. PL/SQL does this “automagicly” and converts the single row inserts in to a bulk operation, which in effect inserts 100s of rows at a time. The insert script that SQL Developer is running is running each insert as a single command. This is a classic “Row by slow” operation. But PL/SQL is slow compared to SQL Loader and as Jeff says, this could be even faster. Using direct path would likely cut the time by a significant amount. Hope this helps – Ric

Unfortunately, a lot of Data Engineers and Data Scientists (say, in an Analytics shop) do not have access to a folder residing on the DB Server and DBAs may not be inclined to give access due to “security” concerns. Hence, having a handy utility to load records super fast is awesome!