How to Rebuild Indexes in Oracle

Leave a reply

Rebuild Index offline and online in Oracle

An index helps speed up data retrieval in a select query, but it can become fragmented due to insert, update, and delete commands. To fix this fragmentation, we need to rebuild the index.

Syntax

-- Basic Syntax
ALTER INDEX index_name REBUILD;

-- Rebuild Index in a Specific Schema
ALTER INDEX schema_name.index_name REBUILD;

-- Rebuild Index ONLINE (Supported by Oracle Enterprise Edition)
ALTER INDEX index_name REBUILD ONLINE;

-- Rebuild with PARALLEL
ALTER INDEX index_name REBUILD PARALLEL 4;

--To reset parallelism:
ALTER INDEX index_name NOPARALLEL;

--Rebuild a Partitioned Index
ALTER INDEX index_name REBUILD PARTITION partition_name;

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 tablespace_name,index_name from dba_indexes where table_name = 'EMPLOYEE'

Script to rebuild all indexes on a table

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;'
FROM dba_indexes
WHERE table_name = 'YOUR_TABLE';

During Rebuild you can also change the tablespace of index

--Online rebuild of index used in Enterprise edition.
ALTER INDEX pk_emp REBUILD ONLINE TABLESPACE users;

--Offline rebuild
ALTER INDEX hr.pk_emp REBUILD TABLESPACE USERS;

Verify all details such as parallel, tablespace name, table name, order (ascending or 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;


Leave a Reply