Alter Index Rebuild Partition to Another Tablespace

Rebuild Index Partition

Yes, index can be partitioned. Since a partitioned index has many segments, we cannot rebuild the partitioned index as a whole. That is, we can only rebuild its individual segment one by one.

First of all, let's check the partition name of the index. For example:

SQL> select partition_name from all_ind_partitions where index_owner = 'SH' and index_name = 'SALES_CUST_BIX' order by 1;

PARTITION_NAME
--------------------------------------------------------------------------------
SALES_1995
SALES_1996
SALES_H1_1997
SALES_H2_1997
SALES_Q1_1998
SALES_Q1_1999
...

Then we choose the right partition name of the index to rebuild. In this case, we choose to rebuild SALES_Q2_1998.

There're 4 scales to rebuild a partitioned index.

1. ALTER INDEX REBUILD PARTITION
2. ALTER INDEX REBUILD PARTITION TABLESPACE
3. ALTER INDEX REBUILD PARTITION TABLESPACE ONLINE
4. ALTER INDEX REBUILD PARTITION TABLESPACE ONLINE PARALLEL

ALTER INDEX REBUILD PARTITION

Without specifying the tablespace clause, the server process rebuild the index partition in the original place (tablespace).

SQL> alter index sh.sales_cust_bix rebuild partition sales_q2_1998;

Index altered.

Please make sure that there're enough space to accommodate the newly-rebuilt index before dropping the old one.

ALTER INDEX REBUILD PARTITION TABLESPACE

To move the index partition to another tablespace, we should specify the tablespace clause.

SQL> alter index sh.sales_cust_bix rebuild partition sales_q2_1998 tablespace users;

Index altered.

Let's see the result.

SQL> select tablespace_name from all_ind_partitions where index_owner = 'SH' and index_name = 'SALES_CUST_BIX' and partition_name = 'SALES_Q2_1998';

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

We have moved it to USERS.

ALTER INDEX REBUILD PARTITION TABLESPACE ONLINE

ONLINE feature can also be applied on rebuilding the index partition.

SQL> alter index sh.sales_cust_bix rebuild partition sales_q2_1998 tablespace users online;

Index altered.

ALTER INDEX REBUILD PARTITION TABLESPACE ONLINE PARALLEL

To speed up the rebuilding, we can add PARALLEL to the statement.

SQL> alter index sh.sales_cust_bix rebuild partition sales_q2_1998 tablespace users online parallel 4;

Index altered.

This won't change the degree of parallelism of the index.