When Does Offline Index Rebuild Refer To Base Table? (Doc ID 278600.1)

Last updated on DECEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.1.0.2 [Release 8.1.7 to 10.1]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

An index rebuilt either Online or Offline.

Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster

So, the base table is not referred for data when the index is rebuilt offline.
This article describes this behavior with test cases and depicts a few scenarios when this is violated.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms