Is it more efficient to drop the index before loading large amout of data then re-create index?
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:
- What kind of indexes? Bitmap, reverse key, compressed, domain?
- How many indexes, how many columns do they have, what is the size of the columns, what is the block size?
- How ordered is the inserted data?
- Can indexes be rebuilt in parallel?
- How much data can fit in the buffer cache (memory)?
- What is the statistics gathering strategy, can stats run with cascade => false?
- 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.