Oracle drop and create index
indexing, oracle
Solution
I don't see a problem with that, but instead of drop/create you could also use the syntax below:
alter index <INDEX_NAME> rebuild tablespace <TABLESPACE_NAME>
To address what you asked in the comment below, the `alter index rebuild` should be faster. The reason for that is when you drop the index and create it again, index tree will be built from the table itself. But with `alter index rebuild`, Oracle reads the index itself, thus resulting in a smaller amount of I/O.
Problem
I would like to know if dropping Oracle index and recreating them will pose any data issues if assuming these are done during scheduled downtime. Recently discovered that some indexes were parked on incorrect table space, would like to correct it by dropping the index and recreating it on the correct table space. Please kindly advise.