Moving a table and rebuilding the indexes at the same time

After moving a table (ALTER TABLE XXX MOVE ), because the physical location of the rows changes, i.e., the rowids of the records change, all indexes on the table will become "UNUSABLE". This means that we have to manually rebuilt them (ALTER INDEX YYY REBUILD).

Is it  possible to move a table and rebuild the indexes at the same time with a single move statement? (Note: I am not considering the case of using the DBMS_REDEFINITION package for on-line table redefinition)


It is not possible when moving the whole table, only for individual partitions/subpartitions. Here is an example:


nkarag@DWHPRD> create table lala as select * from user_tables
  2  /

Table created.


nkarag@DWHPRD> create index lalaidx on lala(table_name)
  2  /

Index created.


nkarag@DWHPRD> select index_name, STATUS from user_indexes where index_name = 'LALAIDX'
  2  /

INDEX_NAME                     STATUS
------------------------------ --------
LALAIDX                        VALID

nkarag@DWHPRD> -- the old way

nkarag@DWHPRD> alter table lala move

  2  /

Table altered.

nkarag@DWHPRD>  select index_name, STATUS from user_indexes where index_name = 'LALAIDX'

  2  /

INDEX_NAME                     STATUS

------------------------------ --------

LALAIDX                        UNUSABLE

nkarag@DWHPRD> -- now we need to rebuild the index

nkarag@DWHPRD> alter index lalaidx rebuild

  2  /

Index altered.

nkarag@DWHPRD> select index_name, STATUS from user_indexes where index_name = 'LALAIDX'

  2  /

INDEX_NAME                     STATUS

------------------------------ --------

LALAIDX                        VALID

nkarag@DWHPRD> -- can we do both with one statement?

nkarag@DWHPRD> alter table lala move update indexes

  2  /

alter table lala move update indexes

                      *

ERROR at line 1:

ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

nkarag@DWHPRD> -- So the clause "update indexes" does not work when moving the whole table

nkarag@DWHPRD> -- It works though for partitioned tables, when you move a specific partition:

nkarag@DWHPRD> drop table lala

  2  /

Table dropped.

nkarag@DWHPRD> l

  1  create table lala

  2  partition by range(created)

  3  (partition P2011 values less than (to_date('01/01/2012','dd/mm/yyyy')),

  4  partition P2012 values less than (to_date('01/01/2013','dd/mm/yyyy'))

  5  )

  6* as select * from user_objects

nkarag@DWHPRD> /

Table created.

nkarag@DWHPRD> create index lalaidx on lala(object_name)

  2  /

Index created.

nkarag@DWHPRD> select index_name, status from user_indexes where index_name = 'LALAIDX'

  2  /

INDEX_NAME                     STATUS

------------------------------ --------

LALAIDX                        VALID

nkarag@DWHPRD> alter table lala move partition P2011 update indexes

  2  /

Table altered.

nkarag@DWHPRD> 

nkarag@DWHPRD> select index_name, status from user_indexes where index_name = 'LALAIDX'

  2  /

INDEX_NAME                     STATUS

------------------------------ --------

LALAIDX                        VALID

nkarag@DWHPRD> -- VOILA!

Cheers,

OL.