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.

Syntax

--Offline rebuild
ALTER INDEX  REBUILD; (default)

-- Online rebuild used in Enterprise 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 Enterprise edition.
ALTER INDEX pk_emp REBUILD ONLINE;

--Offline rebuild
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;


Check Complete information including parallel, tablespace name, table name, ascending, descending, column name etc

set line 250 pages 1000
column b.tablespace_name format a10
column table_name format a10
column index_name format a20
column column_name format a16
column tablespace_name format a8
column last_analyzed format a9
column degree format a1
Select a.table_name, a.index_name, a.column_name,b.tablespace_name,b.last_analyzed,b.degree,b.partitioned,b.status,a.descend
FROM dba_ind_columns a,dba_indexes b where b.index_name = a.index_name and
a.table_owner='IC' and a.table_name = 'TRAN'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;