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:

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

To load a table that has no indexes
  1. Drop the table (if it exists).
  2. Create the table without specifying any unique constraints.
  3. Load all rows into the table.
  4. Alter the table to apply the unique constraints.
  5. 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.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]