What happens to the indexes after "alter table tbl move"?

[Tianhua]

Hi all,

I have two related questions about "alter table ... move " command.

1. If the table I plan to move is a regular table, then the rowid of each column and the relative location of data in the table will change after the move, right? If this is correct, then all the indexes of the table will be unvalid. So does oracle automatically rebuilds the indexes of the table? I couldn't find anything about this from oracle documentation.

2. If the table is partitioned, then according to oracle documentation, all indexes will be marked unusable. What does that mean? Do I have to re-create all indexes after I move a partitioned table?

Please help! Thanks!

Tianhua

[marist89]

Hmm, lets see...

SQL> create table xyz (x char(1), y char(1), z char(1));

Table created.

SQL> create index xyz_indx on xyz(x);

Index created.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

SQL> alter table xyz move tablespace users;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

SQL>  alter table xyz move tablespace tools;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       VALID

At first site, you would say no, oracle rebuilds the indexes. However, when you put data into the table:

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table xyz move tablespace tools;

Table altered.

SQL> select index_name, status from dba_indexes
  2  where table_name = 'XYZ';

INDEX_NAME                     STATUS
------------------------------ --------
XYZ_INDX                       UNUSABLE

Oracle tells us a different story. Yes, this index must be rebuilt. The same lies true for regular indexes on partitioned tables, global indexes, and the affected partition on partitioned tables.

[Tianhua]

Thank you Jeff! You are the man!

Tianhua