Index online operation的過程 zt

在create index online, alter index rebuild online時，oracle是怎麼工作的？

Online Index Operations

Oracle provides the capability to perform online index creation. During an online index create operation, concurrent queries and updates are allowed on the base table. During an online index creation operation, changes are recorded in a temporary journal table. At the end of index creation, the journal is merged into the new index. There are three phases in an online index create operation:

Prepare phase: locks are acquired on the table briefly to create the index structure and place entries in the data dictionary.

Build phase: most work is done in this phase where the index is populated. While the build phase occurs, changes to the table are placed in the journal table. The journal table is automatically created and structured as a B-tree so that multiple operations on the same table data can be easily detected and resolved to the latest change.

Merge phase: rows in the journal table are iterratively merged into the index. Most of the merge operation occurs while the table is still online. As journal rows are merged, they are deleted if they are not locked. During the merge phase, edits to the base table will continue to be tracked in the journal table. If changes to the journal continue to occur before the merge process can complete, Oracle will make additional passes through the journal. If after a few passes, the merge is still not complete, Oracle will lock the table briefly and apply the remainder of the journal. The entire process is transparent to the users and upon completion, the new index is brought online.

Index Rebuild (Move)

Oracle supports online index rebuilding. During an online index rebuild operation, concurrent queries and updates are allowed on the base table. The index rebuild operation requires additional disk space during the build process for the newly created index, but the resulting rebuilt index may be more space efficient. Hence the operation is likely to free up additional disk space.

The algorithm for online index rebuild is similar to that of online index creation, and the same basic phases are executed. During an online index rebuild operation, changes are recorded in a temporary journal table. At the end of index rebuild, the journal is merged into the new rebuilt index.

[@more@]