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
- Leave crowded Tablespace
- Fix Corrupted Logical Structure
- Fix Unusable Indexes
This could reduce the size of the index and might speed up index scanning a little bit during queries.
This could release some space from the crowded tablespace to prevent ORA-01653 for tables or ORA-01654 for indexes.
After frequent data changing, index may not work properly since it has some inconsistencies in the logical structure (i.e. ORA-08102).
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.
- ALTER INDEX REBUILD
- ALTER INDEX REBUILD TABLESPACE
- ALTER INDEX REBUILD ONLINE
- ALTER INDEX REBUILD PARALLEL
- ALTER INDEX REBUILD NOLOGGING
- 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.