Do I need to remove indexes on a table before a large load?

Question

I am working in a data warehouse, and the standard setup for loading a table is to insert rows into the table for the nightly load that only loads changes. Still, if a full history load is run, all the indexes on the dimension or fact table are dropped before the insert and then added back after. Does this make the load run faster? Is there another reason to drop all indexes and then rebuild when loading a large number of rows to a fact or dimension table?

Answer 1

Dropping and recreating indexes are done to increase the data insertion speed. This practice makes sense, especially when loading a huge amount of data. Since the more data volume increases, the total time of data insertion and index recreation becomes smaller than inserting data and updating indexes time.

You can learn more information in the following article:

• More Indexes, Slower Insert