Rebuild Index offline and online in Oracle

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.