I want to use the
alter table move tablespace
command to reorganize tables to remove fragmentation.
Question: Does the "alter table move tablespace" command preserve my 1) index and 2) constraint definitions?
The alter table move tablespace syntax preserves the index and constraint definitions:
The table move will change the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid.
Therefore, the need to execute step two immediately after step one, which is to rebuild the invalid indexes on the current table.
At the same time an advantage of using the
table move procedure
- all constraints are preserved and
- index definitions are also saved,
so that re-indexing is possible using the fast index REBUILD method,
rather than the slower index DROP and CREATE method.