My Oracle Support Banner

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

Last updated on NOVEMBER 29, 2019

Applies to:

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


An index rebuilt either Online or Offline.

Online Index Rebuild Features:
+ 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:
+ 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.


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.