Online Data Reorganization

Database availability and manageability have become increasingly important in many of today's mission-critical environments. In earlier versions of Oracle, creating or rebuilding an index to reduce fragmentation causes the base table to be locked and no DML operation is allowed until the index operation is completed. This can take a long time with large tables. Oracle8 and Oracle8i have features to minimize the impact of data reorganizations to end users.

One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation. Oracle8 introduced partitioned tables and indexes, which allowed administrators to perform maintenance on these objects - one partition at a time - while the other partitions remained online. This minimized the impact of maintenance activities on data availability. Oracle8i improves on this with new features allowing administrators to perform required maintenance while tables and indexes are completely online and fully available to end-users. These new online Oracle8i maintenance capabilities improve data availability, query performance, response time and disk space utilization, all of which are important in a mission-critical environment.

Data Reorganzation Feature Comparison

Oracle8i offers the following online data reorganization capabilities: index create, rebuild, and coalesce, and Index-Organized Table (IOT) move.

Index Create

Both Oracle8 and Oracle8i support traditional index creation against underlying tables. During an index create operation, the base table is locked and no concurrent updates are allowed on the base table, although queries can proceed because of Oracle's read consistency architecture. Oracle8i introduces 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)

Both Oracle8 and Oracle8i support traditional index rebuild. During an index rebuild operation, the base table is locked and no concurrent updates are allowed on the base table, although queries can proceed because of Oracle's read consistency architecture. Oracle8i introduces the capability to do 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.

Online Index Defragmentation

Oracle8i introduces the capability to do online index coalescing or defragmentation. During an online index coalesce operation, concurrent queries and updates are allowed on the base table. Oracle8 does not support online or offline index coalesce.

Online index coalesce is similar to online index rebuild operation. Both reorganize the index and improve space utilization and query performance. The major difference is that online index coalesce is an in-place reorganization and does not require additional disk space. Only adjacent leaf blocks are merged, if possible. The height of B-tree index is not reduced.

The index coalesce operation collapses into a single leaf block adjacent index blocks that contain less than one block of data. Blocks that have been modified since the start time of index coalesce and blocks with uncommitted rows are skipped. Index coalesce does not require extra disk space for an index copy or a journal - both of which are required in the case of index rebuild.

Table Move

Oracle8i introduces the capability to do both a normal table move and an online Index-Organized Table (IOT) table move. During a normal table move operation, the base table is locked and no concurrent updates are allowed on the base table, although queries can proceed because of Oracle's read consistency architecture. During an online IOT table move operation, concurrent queries and updates are allowed on the table. Both types of table move operations require additional disk space during the operation for the new table, but the resulting rebuilt table may be more space efficient due to elimination of rows from the original table. Hence the operation is likely to free up additional disk space.

The algorithm for an online IOT table move is similar to that of online index creation. During an online IOT table move operation, changes are recorded in a journal table. At the end of the table rebuild, the journal is merged into the newly rebuilt table. IOT table move currently does not support parallel operation.

With Oracle8i, IOT has been enhanced with the ability to use logical ROWIDs. Logical ROWIDs contain the primary key of the referenced row, instead of a physical location within the database. This means that if a row is moved to a new location, the logical ROWID still contains a valid reference to the row. To retain the speed of conventional ROWIDs, logical ROWIDs also contain a physical location hint which can be used to optimize performance. If a secondary index is created on a field other than the primary key for an IOT, Oracle8i creates a logical index using logical ROWIDs to reference the table rows. If a table row is moved to a new location, the logical index does not need to be updated.

Both Oracle8 and Oracle8i support the "create table as select" command, which can achieve similar results as a table move command. However, many of the original table attributes like constraints and triggers are lost and need to be reapplied on the new table with this method. The online Oracle8i table move operation preserve all constraints, triggers, grants and indexes.

Also Oracle8i currently supports IOTs with partitions and IOTs with Large Objects (LOBs), but not the combination of IOTs, partitions and LOBs.

Additional Data Reorganization Considerations

Concurrent activity on the table during an online operation. During an online operation, Oracle recommends users minimize activities on the base table. Database activities should impact less than ten percent of the table while online operation is in progress. Also in Oracle8i, a database administer can use the new Database Resource Manager feature of to minimize the data reorganization impact to users by allocating enough resources to users.

Oracle does not recommend running online operations at peak times or running a batch job that modifies large amount of data during an online data reorganization. In fact, parallel DML, direct load and import/export cannot be performed during an online operation.

Performance penalty for DML operations during an online operation. Oracle estimates the performance overhead for DML operations during an online operation is comparable to the performance penalty of maintaining one extra index.

Rebuilding index online vs. dropping an index and then re-creating a new index online. Rebuilding an index online requires additional disk space for the new index during the operation, whereas dropping an index and then recreating an index does not require additional disk space. However, database performance may be impacted during the period when the index is dropped and before the new index is created.

Coalescing an index online vs. rebuilding an index online. Online index coalesce is an in-place data reorganization operation, hence does not require additional disk space like index rebuild does. Index rebuild requires temporary disk space equal to the size of the index plus sort space during the operation. Index coalesce does not reduce the height of the B-tree. It only tries to reduce the number of leaf blocks. The coalesce operation does not free up space for users but does improve index scan performance.

If a user needs to move an index to a new tablespace, online index rebuild is recommended. Index rebuild also improves space utilization, but the index rebuild operation has higher overhead than the index coalesce operation.

Local and global indexes. Oracle8i supports both local and global partitioned indexes with online operations, except with an IOT partitioned global index, which is a secondary index. Oracle8i currently does not support online operation with IOT secondary indexes.

Parallel operation and IOT. Oracle8i currently does not support parallel operations with an IOT. Since the journal table is an IOT, the merge phase of an online operation is not performed in parallel.

Data Reorganization Summary Table