You can improve performance of direct path loads by using temporary storage. After each block is formatted, the new index keys are put in a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed.
During a conventional path load, every time a row is inserted the index is updated. This method does not require temporary storage space, but it does add processing time.
To improve performance on systems with limited memory, use the SINGLEROW parameter. For more information, see "SINGLEROW Option".
If, during a direct load, you have specified that the data is to be presorted and the existing index is empty, then a temporary segment is not required, and no merge occurs—the keys are put directly into the index. See "Optimizing Performance of Direct Path Loads" for more information.
When multiple indexes are built, the temporary segments corresponding to each index exist simultaneously, in addition to the old indexes. The new keys are then merged with the old indexes, one index at a time. As each new index is created, the old index and the corresponding temporary segment are removed.
Oracle Database Administrator's Guide for information about how to estimate index size and set storage parameters
To estimate the amount of temporary segment space needed for storing the new index keys (in bytes), use the following formula:
1.3 * key_storage
In this formula, key storage is defined as follows:
key_storage = (number_of_rows) *
( 10 + sum_of_column_sizes + number_of_columns )
The columns included in this formula are the columns in the index. There is one length byte per column, and 10 bytes per row are used for a ROWID and additional overhead.
The constant 1.3 reflects the average amount of extra space needed for sorting. This value is appropriate for most randomly ordered data. If the data arrives in exactly opposite order, then twice the key-storage space is required for sorting, and the value of this constant would be 2.0. That is the worst case.
If the data is fully sorted, then only enough space to store the index entries is required, and the value of this constant would be 1.0. See "Presorting Data for Faster Indexing" for more information.