Question: What is the difference between a "rebuild index" and the "rebuild index online" operation. When do I consider adding the "online" argument to a rebuild index statement?
Answer: The fundamental difference between an "alter index rebuild" and an "alter index rebuild online" is the access methods for the index data. When doing an "alter index rebuild", Oracle reads the existing index to create the new index. With the "alter index rebuild online" option, Oracle instead performs a full-table scan on the target table, an operation that might cause performance degradation during high DML periods.
Answer: The fundamental difference between an "alter index rebuild" and an "alter index rebuild online" is the access methods for the index data. When doing an "alter index rebuild", Oracle reads the existing index to create the new index. With the "alter index rebuild online" option, Oracle instead performs a full-table scan on the target table, an operation that might cause performance degradation during high DML periods.
- Alter index rebuild online: During a online index rebuild, Oracle will make a snapshot log on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt. Because the "alter index rebuild online" performs a full-table scan, it can be perform using parallel query (a parallel hint), and the online rebuild can also be performed in NOLOGGING mode.
- Alter index rebuild: During a "regular" index rebuild, locking occurs as the existing index is read. Hence, this command is designed for scheduled downtime periods where there is no DML activity.
No comments:
Post a Comment