In this article we will discuss about avoiding index maintenance and insert data faster into the table. Index maintenance is one of the major challenging task during bulk insert operations either using conventional and direct path load. For both the conventional and the direct path, SQL*Loader maintains all existing indexes for a table.
To avoid index maintenance, use one of the following methods:
- Drop the indexes prior to the beginning of the load.
- Mark selected indexes or index partitions as Index Unusable prior to the beginning of the load and use the SKIP_UNUSABLE_INDEXES parameter.
By avoiding index maintenance, you can minimize the amount of time and space required during the data loading operations , in the following ways:
- You can rebuild the indexes one at a time, reducing the amount of sort (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.
- Avoiding index maintenance 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 resort the indexes may be excessive.