Skip to content

How Oracle Rebuild Index to Another Tablespace

  • by

Alter Index Rebuild Tablespace

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 has some advantages in database management:

  • Defragment a slack index
  • This could reduce the size of the index.

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

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.

  1. Rebuild Same Tablespace
  2. Rebuild Another Tablespace
  3. Rebuild Online
  4. Rebuild Parallelly
  5. Rebuild NOLOGGING

1. 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 of current size in the tablespace before doing it.

2. ALTER INDEX REBUILD 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

3. 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.

4. ALTER INDEX REBUILD PARALLEL

We can rebuild the index parallelly.

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

Index altered.

But this also changed the degree of parallelism of the index from some degree, mostly 1 into degree 16.

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

DEGREE
----------------------------------------
16

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

5. 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

Leave a Reply

Your email address will not be published.