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.

You can now specify the ONLINE parameter in a statement.
Here's how online index creation works in Oracle:

1. Use the `ONLINE` keyword with `ALTER INDEX`: To create an index online in Oracle, you use the `ONLINE` keyword within an `ALTER INDEX` statement.
2. Syntax for online index creation:
   

   ALTER INDEX index_name REBUILD ONLINE;
   

3. Rebuilding an existing index: This syntax is used to rebuild an existing index online, making it available for use even while it's being rebuilt

Key points to remember:

• The `ONLINE` keyword is only used with `ALTER INDEX` for rebuilding existing indexes.

Additional considerations:

• Compatibility: Online index rebuilding is supported in Oracle Database 11g and later versions.
• Restrictions: There are certain restrictions and considerations for online index creation, such as tablespace compatibility and index types. It's recommended to consult Oracle documentation for more details.
• Performance: Online index creation can impact database performance, so it's important to plan and monitor the process carefully.

Question: Can the following syntax be used for creating an index ONLINE in Oracle?

CREATE INDEX index_name ON table_name
 (column_list) ONLINE;

The syntax provided can be used to create an index online in Oracle. The `ONLINE` clause in the `CREATE INDEX` statement is a feature provided by Oracle Database that allows the creation of an index while the underlying table remains available for DML operations (INSERT, UPDATE, DELETE).
The syntax for creating an index online in Oracle is as follows:

CREATE INDEX index_name ON table_name (column_list) ONLINE;

In this syntax:

• `index_name` is the name you want to assign to the new index.
• `table_name` specifies the name of the table on which the index is to be created.
• `column_list` includes one or more columns of the table that will be included in the index.

When you use the `ONLINE` keyword, Oracle allows the underlying table to be continuously accessed and modified by other transactions while the index build operation is in progress. This feature is particularly useful in a production environment where it is critical to maintain availability and to minimize the impact on concurrent operations.
However, it's important to note that while using the `ONLINE` option enhances the availability of the table during index creation, it might also have an impact on the duration of the index build and the resources required, as the database needs to manage the concurrent DML operations and the index build simultaneously.

Making an index invisible requires Oracle Database 11g. An invisible index is still maintained by Oracle but is not considered by the query optimizer when determining the execution plan. Be aware that an invisible index may still be used internally by Oracle to prevent locking issues or to enforce constraints. So just making an index invisible is not a completely reliable way to determine if it is used. Here is an example of making an index invisible:

SQL> alter index addr_fk1 invisible;

SQL> alter index addr_fk1 visible;

SQL> alter index addr_fk1 unusable;

This code renders the index unusable, but does not drop it. Unusable means that the optimizer will not use the index and Oracle will not maintain the index as DML statements operate on its table. Furthermore, an unusable index canot be used internally to enforce constraints or prevent locking issues.

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:

ALTER INDEX index_name REBUILD ONLINE;

The SQL statement `ALTER INDEX index_name REBUILD ONLINE;` remains a valid and commonly used operation in Oracle databases. This statement is used for rebuilding an existing index while allowing the underlying table to remain accessible for both reading and writing.
In detail:

1. `ALTER INDEX index_name`: This command is used to modify an existing index. 'index_name' is the name of the index that you intend to rebuild.
2. `REBUILD`: This keyword instructs Oracle to recreate the index. Rebuilding an index can be beneficial in scenarios where the index has become fragmented due to extensive updates, deletes, or insert operations, leading to performance degradation.
3. `ONLINE`: This option ensures that the index rebuild operation is performed online. It allows the database to continue to perform DML (Data Manipulation Language) operations like INSERT, UPDATE, and DELETE on the table associated with the index. The ONLINE option is crucial in high-availability environments where it is important to maintain continuous access to the database.

When executed, this command reorganizes the index structure, potentially improving query performance and efficient use of storage space. However, it's important to be aware that rebuilding an index, especially a large one, can be resource-intensive and may temporarily impact the database's performance. Therefore, it's often recommended to perform such operations during periods of low database activity.

You create an index via the create index command. When you designate a primary key or a unique column during table creation or maintenance, Oracle will automatically create a unique index to support that constraint. The full command syntax for create index uses the following format as follows:

create [bitmap | unique] index index
on table(column [,column]. . .) [reverse];

index must be a unique name and follow the naming conventions of Oracle columns. table is simply the name of the table on which the index will be established, and column is the name of the column being indexed. Bitmap indexes allow you to create useful indexes on columns with very few distinct values. The reverse keyword tells Oracle to reverse the bytes of the indexed value, which may improve the I/O distribution during the insert of many sequential data values. You can establish a single index on multiple columns by listing the columns one after the other, separated by commas. For the following examples, the BOOKSHELF_AUTHOR table was created without a primary key:

create table BOOKSHELF_AUTHOR
(Title VARCHAR2(100),
AuthorName VARCHAR2(50),
constraint TitleFK Foreign key (Title)
references BOOKSHELF(Title),
constraint AuthorNameFK Foreign key (AuthorName)
references AUTHOR(AuthorName));

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