Skip to content
Home » Oracle Database » Schema Object » Alter Index Rebuild to Another Tablespace

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:

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.

  1. ALTER INDEX REBUILD
  2. ALTER INDEX REBUILD TABLESPACE
  3. ALTER INDEX REBUILD ONLINE
  4. ALTER INDEX REBUILD PARALLEL
  5. ALTER INDEX REBUILD NOLOGGING
  6. ALTER INDEX REBUILD COMPRESS

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

ALTER INDEX 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

ALTER INDEX REBUILD COMPRESS

To COMPRESS an index while rebuilding it, we can do it in the following ways.

BASIC

For non-unique keys, we can perform both basic (Prefix Compression) and advanced compression.

SQL> alter index hr.emp_manager_ix rebuild compress;

Index altered.

SQL> select compression from all_indexes where owner = 'HR' and index_name = 'EMP_MANAGER_IX';

COMPRESSION
-------------
ENABLED

ADVANCED

For unique keys, we can only perform advanced compression.

SQL> alter index hr.emp_emp_id_pk rebuild compress advanced;

Index altered

SQL> select compression from all_indexes where owner = 'HR' and index_name = 'EMP_EMP_ID_PK';

COMPRESSION
-------------
ADVANCED HIGH

We compressed the segment and changed the attribute of the index at the same time.

Leave a Reply

Your email address will not be published. Required fields are marked *