Home » SQL & PL/SQL » SQL & PL/SQL » alter table move
alter table move [message #237276] Mon, 14 May 2007 01:24 Go to next message
Messages: 112
Registered: July 2006
Senior Member

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.

Can you help me? Why is the select code slow?

Thanks, Stefan
Re: alter table move [message #237288 is a reply to message #237276] Mon, 14 May 2007 01:39 Go to previous message
Messages: 402
Registered: July 2005
Senior Member
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


Previous Topic: what is hard-coded
Next Topic: create or replace v/s create
Goto Forum:

Current Time: Sat Jul 22 14:09:26 CDT 2017

Total time taken to generate the page: 0.05959 seconds