What happens to the indexes after "alter table tbl move"?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jul 2001
    Posts
    15
    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hmm, lets see...
    Code:
    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:
    Code:
    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.

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2001
    Posts
    15

    Thumbs up

    Thank you Jeff! You are the man!

    Tianhua

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width