Prior to Oracle8i, major modifications to an index, such as the creation or rebuilding of an index, required that a shared lock be placed on the table.
This lock made it easier to implement these operations, but it interfered with the operation of the database, because it effectively prevented write operations to the table during the duration of the operation. Oracle allows you to create, rebuild, and coalesce indexes while the underlying table remains online.
Creating an index online
You can now specify the ONLINE parameter in a CREATE INDEX statement.
The syntax for the statement is illustrated in the following diagram:
One of the ways that indexes offer performance gains is through their physical structure. A B*-tree index makes it possible to locate a row with a randomly accessed value quickly.
Part of database maintenance operation includes periodically rebuilding your index structures. You can perform two basic types of index maintenance:
COALESCE, which reclaims wasted space in the leaf nodes of the index
REBUILD, which completely rebuilds the index
You can now perform both of these operations online, as the following diagram demonstrates: