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:
Required keywords
Unique name for the index
Required keyword
Unique table name
List of columns that make up the index
Required keyword to build index while leaving underlying table available
CREATE INDEX index_name ON table_name
(column_list) ONLINE;
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:
Required keywords
Unique name for the index
Keyword for indicating desired maintenance operation
Required keyword to build the index while leaving the underlying table available
ALTER INDEX index_name REBUILD/COALESCE ONLINE;
Unique name for the index Create Index Online
In the next lesson, you will begin to learn about function-based indexes.