SQL Loader - Oracle 10g/11g Data and Database Management Utilities - page 44

From the tests, meaningful time differences between a conventional and a direct path
load can be shown. So it is worth considering the possibility of loading data in direct
path mode.

Case 5 Case 6

Path Conventional Direct

Primary Key Yes Yes

Extent pre-allocation No No

Time 944s 50s

During this test, a primary key constraint was added. The primary key related index
took 1 minute 16.4 seconds. Considering this, if the table already has indexes, in the
case of direct path load, it takes less time to leave the index and perform the load
than inserting the whole data and rebuilding the index afterwards. In the case of
conventional path loading, if the table already has indexes, it takes about the same
time to complete the load with or without indexes. It should be pointed out that the
more indexes there are, the more effort is required to maintain them. On direct loads,
the indexes are maintained by data saves; it uses temporary extents and then merges
this data to the maintained indexes. On the other hand a conventional path maintains
the indexes with regular transactional procedures. Depending on the number of
indexes it may be better to set them to an unusable state and have them rebuilt after
the load has finished.

Loading Large Objects (LOBs)
There are several ways to load multimedia files. This kind of data is provided in
a raw format, so the most suitable data type to store this information in is: the
Binary Large Object (BLOB ). Both the LONG and LONG RAW are not considered in this
discussion as it is not good practice to preserve these kinds of columns.
The long data type is considered deprecated for the new features; it has been a
constant throughout all the new releases, starting with 8.0. Most of the new features
are supported for LOB data types, but not for LONG data types.