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

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.