During a conventional path load, any indexes on the table being loaded are updated as each row is inserted. This index maintenance adds to the processing load, and of course has a negative impact on the performance of the load. For direct path loads, a different approach is taken to index maintenance in order to minimize the performance impact. Indexes are essentially rebuilt at the end of the load.
During a direct path load, new index keys for the new rows are written to a temporary segment. SQL*Loader will create one temporary segment for each index on the table being loaded. At the end of the load, each temporary segment is sorted, and then merged with the corresponding old index in order to create a new index. After the new indexes are created, the old indexes and the temporary segments are removed. Merging all the new index entries at once like this usually requires less processing overhead than would be required to insert each new index entry one at a time. The exception to this is when you load a very small number of rows into a large table. In such a case, it might be more advantageous to update the indexes in the traditional manner rather than rebuild them. You can use SQL~Loader?s SINGLEROW option?described later in this chapter?to do that.