Beware of ALTER TABLE MOVE Command

Yesterday, i read about Rebuilding of Table in "Practical Oracle 8i" By Jonathan Lewis, it was well explained. Very good book, yes its my all time favorite book.
I would like to post an article about the possible ways of rebuilding table (mainly with the help of MOVE command) and its impacts.

Oracle provides many ways to reorganize/rebuild a table,

alter table move

dbms_redefinition

ctas (ie. create table as select...)

exp/imp

expdp/impdp (datapump)

Here some more explanation about alter table move command, The alter table move command allows us to change the tablespace, storage clauses, lowers highest watermark etc.

If you have to rebuild a table, always consider the move command first. Availability can be reduced (During Move command execution, no transaction should refer on the same object else we may get ORA-08103: Object no longer exists), but the benefit to administrative safety and convenience is huge. There are no windows of opportunity for data to go missing.

Now we will see an impact of MOVE command,
Afer the execution of move command, the dependent indexes will become INVALID/UNUSABLE since it changes the ROWIDs of the table rows. So we have to rebuild a dependent indexes immediately by the below command.

ALTER INDEX index_name REBUILD;

Here is my experiment,

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t
  2  as
  3  select * from all_objects;

Table created.

SQL> insert into t select * from t;

32659 rows created.

SQL> insert into t select * from t;

65318 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t on t(id);

Index created.

SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME       INDEX_TYPE        STATUS
---------------- ----------------- --------
IDX_T            NORMAL            VALID

SQL> set linesize 200;
SQL> set autotrace traceonly explain;

SQL> select * from t where object_id=9;


Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)


SQL> set autotrace off;

SQL> alter table t move;

Table altered.


SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME      INDEX_TYPE    STATUS
--------------- ------------- --------
IDX_T           NORMAL        UNUSABLE

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    24 |  3072 |   478   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |    24 |  3072 |   478   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

SQL> alter index idx_t rebuild;

Index altered.

SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

The conclusion is, after execution of move command, the dependent indexes should be rebuilt immediately.

Hope you have enjoyed the experiment.

Please post your comments (if any).