I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
I’ll show you what happens during an online rebuild of an index. Few DBAs realize this, but during this operation an auxiliary index (among other things) is created to store the transient data while the application continues to run normally.

Note that for the ORDER_ITEMS table we have only 3 indexes whose names are listed in the query:

Also note that we have 27 segments of the INDEX type that belong to the SOE:

In another session I run the online rebuild operation of the ORDER_ITEMS_PK index:

Note that a new segment of type INDEX was created with the random name SYS_IOT_TOP_117714. This is the index that is storing the transient data for DMLs operations that updated the index being rebuilt:

The online rebuild operation is completed in the other session:

Note that the SYS_IOT_TOP_117714 index no longer exists after completing the data merge:

Some insights during rebuild online
I noticed some more segments created during the online rebuild of the index in question …

Before starting the operation, I have listed all segments except PARTITION:

Notice that we have 32 segments of the SOE in total, not counting the partitioned ones.

I started rebuild online and I followed.

At first the SYS_IOT_TOP_117714 index is created:

Soon another segment is created, this one of type TEMPORARY, with the name 10.256074:

The SYS_IOT_TOP_117714 and 10.256074 segments are now removed, and two new TEMPORARY-type segments are created 10.256050 and 10.256058:

At the end of the rebuild online operation everything returns to the initial state:

One more thing I noticed is that an object of type TABLE, in my case with the name SYS_JOURNAL_117616, is created in the data dictionary throughout the operation and it exists at all times along with the index, since both form an IOT:

Confirming then the IOT:

An investigation I thought it was cool to do. The goal was only to show that an auxiliary index was created during rebuild online.

In this test I used version 12.1.0.2 of the Oracle Database, so there may be divergences for other versions.

Throughout the test the Swingbench has been running to generate load and containment in the environment.

Online rebuild operations can only be performed on Oracle Database Enterprise Edition. In the case of common rebuild, the remaining sessions that attempt to use the table in which the index is being rebuilt will wait for the operation to complete and display the “enq: TM – contention” event.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.