Alter Index Rebuild to Another Tablespace

Move Index

To move an index is to rebuild a whole index either in the original tablespace or another tablespace with some options like ONLINE or PARALLEL. In fact, rebuilding index in Oracle has some advantages and usages in database management:

Defragment a slack index

This could reduce the size of the index and might speed up index scanning a little bit during queries.

Leave crowded Tablespace

This could release some space from the crowded tablespace to prevent ORA-01653 for tables or ORA-01654 for indexes.

Fix Corrupted Logical Structure

After frequent data changing, index may not work properly since it has some inconsistencies in the logical structure (i.e. ORA-08102).

Fix Unusable Indexes

Index that is marked as UNUSABLE causes some operations failed with ORA-01502. The formal way to solve it is to exactly rebuild the index.

For rebuilding a partitioned index, there're other ways to do it.

Suppose we'd like to rebuild index named OE.ORDER_PK by ALTER INDEX syntax, which is in EXAMPLE tablespace currently.


SQL> conn oe/oe@orcl

Connected.

SQL> select tablespace_name from user_indexes where index_name = 'ORDER_PK';



TABLESPACE_NAME

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

EXAMPLE

In this post, I introduce 5 various ways to move an index.

Rebuild Same Tablespace
Rebuild Another Tablespace
Rebuild Online
Rebuild Parallelly
Rebuild NOLOGGING

ALTER INDEX REBUILD

If you want to rebuild the index in the same tablespace, you don't have to specify tablespace clause.


SQL> alter index ORDER_PK rebuild;



Index altered.



SQL> select tablespace_name from user_indexes where index_name = 'ORDER_PK';



TABLESPACE_NAME

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

EXAMPLE

The only purpose of rebuilding an index in the original tablespace is defragmentation. You'd better make sure that there's enough free space, at least twice (2x) of current size in the tablespace before doing it.

ALTER INDEX REBUILD TABLESPACE

To move index to another tablespace, you need to specify the tablespace clause in order to move the index out of the original place.


SQL> alter index ORDER_PK rebuild tablespace USERS;



Index altered.



SQL> select tablespace_name from user_indexes where index_name = 'ORDER_PK';



TABLESPACE_NAME

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

USERS

ALTER INDEX REBUILD ONLINE

Since Oracle 10g, we are able to move indexes with ONLINE option, which means data manipulation language (DML) are allowable to perform during rebuilding of indexes.


SQL> alter index ORDER_PK rebuild tablespace USERS online;



Index altered.

ONLINE option does not mean that rebuilding an index needn't lock any object, it just locked the table and then release it in a very short time in the last phase.

Be careful, REBUILD keyword in ALTER INDEX cannot be specified after tablespace clause.

ALTER INDEX REBUILD PARALLEL

We can rebuild the index parallelly.


SQL> alter index ORDER_PK rebuild tablespace USERS online parallel 16;



Index altered.

Actually, it's a ALTER INDEX REBUILD ONLINE PARALLEL or ALTER INDEX REBUILD PARALLEL ONLINE statement.

No like ALTER TABLE MOVE, rebuilding an index parallelly changes the degree of parallelism of the index.


SQL> select degree from user_indexes where index_name = 'ORDER_PK';



DEGREE

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

16

NOPARALLEL

If any SQL execution path related to the index is affected or worse, you may revert it back like this.


SQL> alter index ORDER_PK noparallel;



Index altered.



SQL> select degree from user_indexes where index_name = 'ORDER_PK';



DEGREE

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

1

Rebuild NOLOGGING

If the database has no reason to keep redo logs of the index, or it's in NOARCHIVELOG mode currently. We can speed up the rebuilding by reducing online logging, if the index is really big.

By the way, do you know how to check archived log mode?


SQL> alter index ORDER_PK rebuild tablespace USERS online nologging;



Index altered.

Again, NOLOGGING permanently changed the attribute of the index from LOGGING into NOLOGGING.


SQL> select logging from user_indexes where index_name = 'ORDER_PK';



LOG

---

NO

To revert it back to normal, you can do it like this:


SQL> alter index ORDER_PK logging;



Index altered.



SQL> select logging from user_indexes where index_name = 'ORDER_PK';



LOG

---

YES