Table moved to new tablespace on different disk

[bigdeal]

Hi all, If I move a table to a different (new) tablespace do I need to gather stats or rebuild the indices (even though the indices are on a seperate tablespace from the table)?

[shreyaas]

hi,
i think that yes you do need to rebuild the indexes..reason..that now the table is in new tablespace..the physical locaiton has changed ..so index has to store the rowids and those rowids have changed..so you must rebuild the indexes..
shreyaas

[jkoopmann]

how did you do the move will indicate if you need to rebuild indexes or any other supporting structures.

you can always check the dba_indexs view to see the associated indexes on a particular table.

select index_name
from dba_indexes
where table_owner = :table_owner
and table_name = :table_name

[sagirk]

Once you move the table to a new tablespace, you will need to rebuild the indexes before using else it will give you error.

Once you have rebuilt the indexes, you can gather statistics.

Regards,
Sagi.