|
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. |
||||
|
|
|
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 |
|||
|
|
