Couple of days ago, as per devt. team request, I had split a partition of one of the partitioned tables, which was having local indexes, fortunately, the partition which I split was belong to current month partition and all the indexes status became unusable and I had to rebuild them.
I was surprised to see that while index rebuilding, it has taken double space than the original one and released 50% occupaid space after the index rebuild. I was suprised because, I was an impression that only index rebuild online would take double space because it create a jounral table and put the data there untile index rebuild finishes.
--- The reason for taking double space while rebuilding is :
"For a rebuild, the index has to be scanned, the results sorted, and new extents built
to hold the newly constructed index. These extents must co-exist with the original index until the process is complete and the original can be dropped.Thus, the rebuild requires a sort and sufficient free space to hold two versions of the index temporarily."
I have found few good notes on rebuild index offline/online, its pros and cons of rebuild indexes. Thought, it would be helpful to others.
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.
When an index is rebuilt offline there is no FTS on the base table. When index is rebuilt online all the blocks from the base table are accessed.
These conclusions are when we donot make an scenario when the index is unusable and then there
is data load to the base table, and finally the index is rebuilt.
As documentation says, while REBUILDing an index OFFLINE, the base table is not referred. But there are situations where the base table is accessed similar to an index create, they are:
+ index is made "unusable"
+ data is loaded with sql loader with skip_unusable_indexes = TRUE
+ index is REBUILD OFFLINE
Basically, when an index is made "unusable", it is normally meant that it will not be used at all (that is drop later) or it has to be drop/create. Here the index being unusable takes more priority than the Offline rebuild of it. An unusable index has to refer the base table while rebuilding.