Index Enhancements   «Prev  Next»

Lesson 8 Rebuilding and defragmenting indexes online
Objective Use the ONLINE parameter for indexes.

Rebuilding and Defragmenting Indexes Online

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:
CREATE INDEX
  1. Required keywords
  2. Unique name for the index
  3. Required keyword
  4. Unique table name
  5. List of columns that make up the index
  6. Required keyword to build index while leaving underlying table available
CREATE INDEX index_name ON table_name
  (column_list) ONLINE;
Required keywords

Alter Index Online

Maintaining an index 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:
  1. COALESCE, which reclaims wasted space in the leaf nodes of the index
  2. REBUILD, which completely rebuilds the index

You can now perform both of these operations online, as the following diagram demonstrates:

COALESCE
  1. Required keywords
  2. Unique name for the index
  3. Keyword for indicating desired maintenance operation
  4. 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.

Rebuilding Defragmenting Indexes Online - Exercise

Click the Exercise link below to practice rebuilding an index online.
Rebuilding Defragmented Indexes Online - Exercise

Indexing using an Oracle Database