Oracle SQL*Loader: The Definitive Guide

Chapter 10. Direct Path Loads

All the SQL*Loader examples that we’ve discussed in previous chapters have been conventional path loads. These use the standard SQL interface to load data into an Oracle database—in other words, conventional INSERT statements are used. Conventional path loads involve all the overhead of standard SQL statement processing. Consequently, severe bottlenecks may occur while loading large amounts of data. To help you avoid these bottlenecks, Oracle provides another load mechanism—the direct path load—that bypasses standard SQL processing. Under the right circumstances, direct path loads can be orders of magnitudes faster than conventional path loads. However, like any other performance-enhancing feature, direct path loads come with a set of tradeoffs.

What is the Direct Path?

You really have to understand direct path loads in terms of what they are not. Let’s look at how a conventional path load works. When you invoke SQL*Loader to load data from a file into an Oracle database, SQL*Loader goes through the process shown in Figure 10-1.

Figure 10-1. A conventional path load

A direct path load works differently. Rather than load data using a series of INSERT statements, a direct path load results in data being written directly into the database datafiles. SQL*Loader reads the input data, formats it into database blocks that match the Oracle data block format, and ...