Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I drop a large index and re-create it, does that increase the size of the datafile where the index is stored?

When I drop an index, it's quite fast. However, when I build one, it takes time, and when checking free space on the datafile, I see it has been reduced.

share|improve this question
up vote 1 down vote accepted

Assuming you are creating the index in the same tablespace that it was previously in, the size of the data files associated with that tablespace on disk should not change and the free space in the tablespace should not decrease.

Of course, normally you are dropping the index so that you can do a large data load, and then rebuilding the index. If that is the case, assuming the table (or the partition you're loading into) and index are in the same tablespace, it is likely that the bulk load will use the space in the tablespace that the index had previously been using so when you go to recreate the index so the data file may need to grow when you build the index even if the index is no larger than it was. If you are doing a bulk load, it is also likely that the index will be larger when it is recreated simply because the underlying table is larger and the size of the index is proportional to the size of the table (of course, the size of the index increases as O( log(n) ))

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.