Difference between Oracle 10g, 11g with regard to Index Rebuild Online

Creating or Rebuilding Indexes Online:

Online Index Rebuilds allows you to perform DML operations on the base table during index creation. You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment.

During index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table.

The ALTER INDEX REBUILD ONLINE can be used to rebuild the index, resuming failed operations, performing batch DML, adding stop words to index or for optimizing the index.

The CREATE INDEX ONLINE and ALTER INDEX REBUILD ONLINE options have been there for a long time to easy the task of online index rebuilding. However in highly active they still can introduce locking issues.

Table Locks:

A table lock is required on the index base table at the start of the CREATE or REBUILD process to guarantee DD information. A lock at the end of the process is also required so as to merge changes into the final index structure.

The time taken to complete the indexing process will increase as the indexing process will hang if there is an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required. Another important issue to be considered is that any other transaction taking place on the base table that started after indexing process will also be locked unless the indexing releases its locked.

This issue can have serious impact on the response time in highly concurrent systems. This backlog of locked transactions can be quite significant depending on the time taken by initial transactions to commit or rollback.

Oracle 11g

Oracle11g has provided enormous improvements in the locking implications regarding creating or rebuilding indexes online. Creating or Rebuilding Indexes Online in Oracle 11g also requires two associated locks on the base table. One lock is required at the start of indexing process and other at the end of indexing process.

The indexing process still hangs until all prior transactions have been completed if an active transaction is going on the base table at the time one of these locks is required.

However such transaction will no longer be locked and complete successfully if the indexing process has been locked out and subsequent transactions relating to the base table starts afterwards.

In Oracle 11g the indexing process no longer effects other concurrent transactions on the base table. The only process potentially left hanging while waiting to acquire its associated lock resource. Now we will compare the index rebuild locking mechanism in Oracle 10g and Oracle 11g.

The difference on ground (10g vs 11g)

Session No.	Executing in 10g	Executing in 11g
1	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.
2	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.
1	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)
3	INSERT INTO MYTABLE VALUES(200002, ‘Again No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘Again No Name’); 1 row created.
2	COMMIT; Commit Complete.	COMMIT; Commit Complete.
2	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.
3	COMMIT; Commit Complete.	COMMIT; Commit Complete. (Session 2) Index Altered (Session 1)

To conclude I would say that even if an index rebuild process is performed online still it requires a lock at the start and at the end of the index rebuild process and these locks eventually causes other concurrent transactions on the table to be hanged.

Therefore an Online Index Rebuilding in Oracle 11g can be still affected by concurrent transactions. However it will not in turn cause locking issues for other concurrent transactions on the base table.