Rebuild Index offline and online in Oracle
Index is used in select query to fetch the data faster but due to insert, update and delete commands index is fragmented to remove the fragmentation of index we need to rebuild the index.
ALTER INDEX REBUILD; (default)
-- Online rebuild used in EE edition.
ALTER INDEX REBUILD ONLINE;
Note: During offline rebuild the table is not used for transaction purpose.
Example of rebuild index
--Online rebuild of index used in EE edition.
ALTER INDEX pk_emp REBUILD ONLINE;
ALTER INDEX hr.pk_emp REBUILD;
Check index present on table
select INDEX_NAME,TABLE_NAME from dba_indexes where table_name = 'EMP';
During Rebuild you can also change the tablespace of index
ALTER INDEX REBUILD TABLESPACE users;