Dropping Indexes for Table-Update Efficiency

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Table Performance Considerations > Changing Tables >

Dropping Indexes for Table-Update Efficiency

In some applications, you can confine most table updates to a single time period. You can set up your system so that all updates are applied overnight or on specified dates.

When updates are performed as a batch, you can drop all nonunique indexes while you make updates and then create new indexes afterward. This strategy can have two positive effects:

The updating program runs much faster if it does not have to update indexes at the same time that it updates tables.
Re-created indexes are more efficient.

For more information about when to drop indexes, see Dropping Indexes.

To load a table that has no indexes

Drop the table (if it exists).
Create the table without specifying any unique constraints.
Load all rows into the table.
Alter the table to apply the unique constraints.
Create the nonunique indexes.

If you cannot guarantee that the loaded data satisfies all unique constraints, you must create unique indexes before you load the rows. You save time if the rows are presented in the correct sequence for at least one of the indexes. If you have a choice, make it the row with the largest key. This strategy minimizes the number of leaf pages that must be read and written.