Is it more efficient to drop the index before loading large amout of data then re-create index?

Question

I read those sentences from the book Use The Index

Nevertheless, the performance without indexes is so good that it can make sense to temporarily drop all indexes while loading large amounts of data—provided the indexes are not needed by any other SQL statements in the meantime. This can unleash a dramatic speed-up which is visible in the chart and is, in fact, a common practimce in data warehouses.

I could understand that creating index after data insert to an empty table is more efficient way. However, Although inserting data to an indexed table is slower,I think re-creating the index is also very expensive.

So my question is, is it also quicker when large amout of rows already exists in the table before inserting? If yes, How much ratio of new rows it is suitable? How to define the 'Large'?

Answer 1

It depends. Only way to be sure is to try it out with your (test) data and in your (test) enviroment.

Answer 2

As others have said, "it depends". But it's worth explaining why such a seemingly simple question is so complicated.

Other than the percent of data added, there are many factors that must be considered:

1. What kind of indexes? Bitmap, reverse key, compressed, domain?
2. How many indexes, how many columns do they have, what is the size of the columns, what is the block size?
3. How ordered is the inserted data?
4. Can indexes be rebuilt in parallel?
5. How much data can fit in the buffer cache (memory)?
6. What is the statistics gathering strategy, can stats run with cascade => false?
7. Explain plan can potentially answer half this question. It can be used to predict the time to build an index. But the time estimates are notoriously bad, and Oracle does not even attempt to estimate the cost of INSERT STATEMENT.

And there are likely other issues I haven't thought of.