Why would I use SQL*Loader conventional load paths?

Question

My understanding of SQL*Loader's conventional path is that it simply generates INSERT statements and sends them to the database. Are there any performance benefits to using this rather than just generating the SQL statements programmatically and executing them against the database?

Answer 1

SQL*Loader is generating INSERT statements, but very critically, it is using bind variables. If you are loading thousands of rows, building up INSERT statements which include string literals will be an order of magnitude slower than using bind variables in addition to thrashing the shared pool. If you generate a bunch of INSERT statements, Oracle has to parse each and every statement-- that will quickly consume the vast majority of the time of your load process. Depending on the size of your shared pool, your CURSOR_SHARING parameter, and the number of rows you are loading, a file of insert statements can very easily put enough pressure on the shared pool that the load process (and/or some other unrelated process that happens to be running at the same time that needs to parse a new query) will error out because there isn't enough contiguous space in the shared pool.

You can certainly write an application that behaves as well as SQL*Loader for a conventional path load. Your application would need to do something like

Prepare the statement
Loop
  Read the next row of data
  Split the next row of data into columns
  Bind the data to the bind variables in the prepared statement
  Execute the prepared statement
End loop

That's very different than just unleashing thousands of separate INSERT statements against the database.