Clever Geek Handbook

Why should I use the regular SQL * Loader load paths?

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

+2


source to share


1 answer


SQL * Loader generates INSERT statements but uses bind variables very critically. If you are loading thousands of lines, then generating INSERT statements containing string literals will be an order of magnitude slower than using bind variables in addition to the spoofed shared pool. If you are building a bunch of INSERT statements, Oracle must parse each statement, which will quickly consume most of the time in your boot process. Depending on the size of your shared pool, your parameterCURSOR_SHARING

and the number of rows being loaded, the insert instruction file can very easily put enough pressure on the shared pool so that the loading process (and / or some other unrelated process that must run concurrently with the need to parse a new request) will crash because there is not enough contiguous in the shared pool space.

You can of course write an application that behaves as well as a SQL * Loader for normal path loading. Your application will 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

This is not at all like unrolling thousands of separate INSERT statements against the database.

+5


source






More articles:


All Articles