Oracle7 Server Utilities

Dropping Indexes

For both the conventional path and the direct path, SQL*Loader builds all existing indexes for a table. The only way to avoid building an index is to drop it before the load and re-create it afterwards.

Dropping and re-creating indexes is one way to save temporary storage while using the direct load facility. This action minimizes the amount of space required during the load, for the following reasons:

You can build multiple indexes one at a time, saving the temporary segment space that would otherwise be needed for each index.

Only one index segment exists when an index is built, instead of the three segments that temporarily exist when the new keys are merged into the old index to make the new index.

This approach is quite reasonable when the number of rows to be loaded is large compared to the size of the table. But if relatively few rows are added to a large table, then the time required to re-sort the indexes may be excessive. In such cases, it is usually better to make use of the conventional path.