MOVE

Re: alter table move

Processing the code


select * from tab where P1 = 110

last a few seconds. After the code


alter table tab enable row movement
alter table tab move

the select-code above last about an hour! I thought this might have to do with the index (does the move-statement affect the index?), but when I run the code


create index Ix_P1 on tab (P1)

I get the ORA-00955: name is already used by an existing object.


Re: alter table move


When you do alter table .. move all the indexes goes into unusable state.
You need to rebuild all the indexes of the table after issuing alter table move commands....

SQL> create table dummy(x int);
Table created.

SQL> create index dummy_idx on dummy(x);

Index created.

SQL> insert into dummy values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> alter table dummy move;

Table altered.

SQL> select index_name,status from user_indexes where table_name = 'DUMMY';

INDEX_NAME STATUS
------------------------------ --------
DUMMY_IDX UNUSABLE