Must Dos after Moving a Table

Moving a table is actually a reorganization process, The database will copy the original data to the new place. In which, the data will be stored optimally in terms of performance and space.

But if you found the performance was degraded on this table after moving, it might be that your indexes have not been rebuilt.

In this post, I will guide you through finding dependent indexes and rebuild them.

Moving a table from EXAMPLE to USERS.

SQL> select tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES';

TABLESPACE_NAME
------------------------------
EXAMPLE

SQL> alter table hr.employees move tablespace users;

Table altered.

SQL> select tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES';

TABLESPACE_NAME
------------------------------
USERS

See what indexes depends on this table.

SQL> column index_name format a30;
SQL> column tablespace_name format a30;
SQL> column status format a10;
SQL> select index_name, tablespace_name, status from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES';

INDEX_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------
EMP_JOB_IX                     EXAMPLE                        UNUSABLE
EMP_DEPARTMENT_IX              EXAMPLE                        UNUSABLE
EMP_MANAGER_IX                 EXAMPLE                        UNUSABLE
EMP_NAME_IX                    EXAMPLE                        UNUSABLE
EMP_EMAIL_UK                   EXAMPLE                        UNUSABLE
EMP_EMP_ID_PK                  EXAMPLE                        UNUSABLE

6 rows selected.

As you can see, all dependent indexes are UNUSABLE. This means, the database will not rebuild them automatically. You have to do it by yourself.

Compose all rebuild statements, and then execute them.

SQL> select 'alter index ' || owner || '.' ||index_name || ' rebuild tablespace users;' as SQL_TO_BE_EXECUTED from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES';

SQL_TO_BE_EXECUTED
--------------------------------------------------------------------------------
alter index EMP_JOB_IX rebuild tablespace users;
alter index EMP_DEPARTMENT_IX rebuild tablespace users;
alter index EMP_MANAGER_IX rebuild tablespace users;
alter index EMP_NAME_IX rebuild tablespace users;
alter index EMP_EMAIL_UK rebuild tablespace users;
alter index EMP_EMP_ID_PK rebuild tablespace users;

6 rows selected.

Or you can rebuild indexes to the original tablespace.

SQL> select 'alter index ' || owner || '.' ||index_name || ' rebuild tablespace ' || tablespace_name || ';' as SQL_TO_BE_EXECUTED from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES';

SQL_TO_BE_EXECUTED
--------------------------------------------------------------------------------
alter index HR.EMP_DEPARTMENT_IX rebuild tablespace EXAMPLE;
alter index HR.EMP_NAME_IX rebuild tablespace EXAMPLE;
alter index HR.EMP_MANAGER_IX rebuild tablespace EXAMPLE;
alter index HR.EMP_EMP_ID_PK rebuild tablespace EXAMPLE;
alter index HR.EMP_EMAIL_UK rebuild tablespace EXAMPLE;
alter index HR.EMP_JOB_IX rebuild tablespace EXAMPLE;

6 rows selected.

Please note that, we rebuild the indexes in the new tablespace USERS. That is to say, for indexes, REBUILD is equivalent to MOVE in tables.

Check the status after rebuilding.

SQL> select index_name, tablespace_name, status from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES';

INDEX_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------
EMP_JOB_IX                     USERS                          VALID
EMP_DEPARTMENT_IX              USERS                          VALID
EMP_MANAGER_IX                 USERS                          VALID
EMP_NAME_IX                    USERS                          VALID
EMP_EMAIL_UK                   USERS                          VALID
EMP_EMP_ID_PK                  USERS                          VALID

6 rows selected.

All indexes become VALID. We're done.