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:

SYS@orcl > select index_name, table_name from dba_indexes where owner='SOE' and table_name='ORDER_ITEMS';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
ITEM_ORDER_IX ORDER_ITEMS
ITEM_PRODUCT_IX ORDER_ITEMS
ORDER_ITEMS_PK ORDER_ITEMS

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

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 33.9375
ADDRESS_PK INDEX 32.8125
CARDDETAILS_CUST_IX INDEX 35.875
CARD_DETAILS_PK INDEX 32.875
CUSTOMERS_PK INDEX 21.6875
CUST_ACCOUNT_MANAGER_IX INDEX 23.8125
CUST_DOB_IX INDEX 26.1875
CUST_EMAIL_IX INDEX 50.375
CUST_FUNC_LOWER_NAME_IX INDEX 37.5625
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 161.25
ITEM_PRODUCT_IX INDEX 107.1875
ORDER_ITEMS_PK INDEX 122
ORDER_PK INDEX 46.1875
ORD_CUSTOMER_IX INDEX 39.3125
ORD_ORDER_DATE_IX INDEX 43.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 44.375
PRD_DESC_PK INDEX .0625
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

27 rows selected.

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

SYS@orcl > alter index SOE.ORDER_ITEMS_PK rebuild online;

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:

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX'order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 33.9375
ADDRESS_PK INDEX 32.8125
CARDDETAILS_CUST_IX INDEX 35.875
CARD_DETAILS_PK INDEX 32.875
CUSTOMERS_PK INDEX 21.6875
CUST_ACCOUNT_MANAGER_IX INDEX 23.8125
CUST_DOB_IX INDEX 26.1875
CUST_EMAIL_IX INDEX 50.375
CUST_FUNC_LOWER_NAME_IX INDEX 37.5625
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 161.25
ITEM_PRODUCT_IX INDEX 107.1875
ORDER_ITEMS_PK INDEX 122
ORDER_PK INDEX 46.1875
ORD_CUSTOMER_IX INDEX 39.3125
ORD_ORDER_DATE_IX INDEX 43.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 44.375
PRD_DESC_PK INDEX .0625
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
SYS_IOT_TOP_117714 INDEX 3.125
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

28 rows selected.

The online rebuild operation is completed in the other session:

Index altered.

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

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 33.9375
ADDRESS_PK INDEX 32.8125
CARDDETAILS_CUST_IX INDEX 35.875
CARD_DETAILS_PK INDEX 32.875
CUSTOMERS_PK INDEX 21.6875
CUST_ACCOUNT_MANAGER_IX INDEX 23.8125
CUST_DOB_IX INDEX 27.1875
CUST_EMAIL_IX INDEX 50.375
CUST_FUNC_LOWER_NAME_IX INDEX 37.5625
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 161.25
ITEM_PRODUCT_IX INDEX 107.1875
ORDER_ITEMS_PK INDEX 120
ORDER_PK INDEX 47.1875
ORD_CUSTOMER_IX INDEX 41.3125
ORD_ORDER_DATE_IX INDEX 43.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 44.375
PRD_DESC_PK INDEX .0625
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

27 rows selected.

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:

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 72.9375
ADDRESS_PK INDEX 64.8125
CARDDETAILS_CUST_IX INDEX 39.875
CARD_DETAILS_PK INDEX 64.875
CUSTOMERS_PK INDEX 42.6875
CUST_ACCOUNT_MANAGER_IX INDEX 28.8125
CUST_DOB_IX INDEX 47.1875
CUST_EMAIL_IX INDEX 61.375
CUST_FUNC_LOWER_NAME_IX INDEX 45.5625
INVENTORIES TABLE 80
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 193.25
ITEM_PRODUCT_IX INDEX 187.1875
ORDERENTRY_METADATA TABLE .0625
ORDER_ITEMS_PK INDEX 160
ORDER_PK INDEX 62.1875
ORD_CUSTOMER_IX INDEX 64.3125
ORD_ORDER_DATE_IX INDEX 62.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 60.375
PRD_DESC_PK INDEX .0625
PRODUCT_DESCRIPTIONS TABLE .3125
PRODUCT_INFORMATION TABLE .25
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
WAREHOUSES TABLE .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

32 rows selected.

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:

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 72.9375
ADDRESS_PK INDEX 64.8125
CARDDETAILS_CUST_IX INDEX 40.875
CARD_DETAILS_PK INDEX 64.875
CUSTOMERS_PK INDEX 42.6875
CUST_ACCOUNT_MANAGER_IX INDEX 29.8125
CUST_DOB_IX INDEX 47.1875
CUST_EMAIL_IX INDEX 62.375
CUST_FUNC_LOWER_NAME_IX INDEX 46.5625
INVENTORIES TABLE 80
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 193.25
ITEM_PRODUCT_IX INDEX 195.1875
ORDERENTRY_METADATA TABLE .0625
ORDER_ITEMS_PK INDEX 160
ORDER_PK INDEX 62.1875
ORD_CUSTOMER_IX INDEX 64.3125
ORD_ORDER_DATE_IX INDEX 63.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 61.375
PRD_DESC_PK INDEX .0625
PRODUCT_DESCRIPTIONS TABLE .3125
PRODUCT_INFORMATION TABLE .25
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
SYS_IOT_TOP_117714 INDEX .0625
WAREHOUSES TABLE .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

33 rows selected.

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

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
10.256074 TEMPORARY 0
ADDRESS_CUST_IX INDEX 72.9375
ADDRESS_PK INDEX 64.8125
CARDDETAILS_CUST_IX INDEX 40.875
CARD_DETAILS_PK INDEX 64.875
CUSTOMERS_PK INDEX 42.6875
CUST_ACCOUNT_MANAGER_IX INDEX 29.8125
CUST_DOB_IX INDEX 47.1875
CUST_EMAIL_IX INDEX 62.375
CUST_FUNC_LOWER_NAME_IX INDEX 46.5625
INVENTORIES TABLE 80
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 193.25
ITEM_PRODUCT_IX INDEX 195.1875
ORDERENTRY_METADATA TABLE .0625
ORDER_ITEMS_PK INDEX 160
ORDER_PK INDEX 62.1875
ORD_CUSTOMER_IX INDEX 64.3125
ORD_ORDER_DATE_IX INDEX 63.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 61.375
PRD_DESC_PK INDEX .0625
PRODUCT_DESCRIPTIONS TABLE .3125
PRODUCT_INFORMATION TABLE .25
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
SYS_IOT_TOP_117714 INDEX .125
WAREHOUSES TABLE .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

34 rows selected.

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:

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
10.256050 TEMPORARY .25
10.256058 TEMPORARY 160
ADDRESS_CUST_IX INDEX 72.9375
ADDRESS_PK INDEX 64.8125
CARDDETAILS_CUST_IX INDEX 40.875
CARD_DETAILS_PK INDEX 64.875
CUSTOMERS_PK INDEX 42.6875
CUST_ACCOUNT_MANAGER_IX INDEX 29.8125
CUST_DOB_IX INDEX 47.1875
CUST_EMAIL_IX INDEX 62.375
CUST_FUNC_LOWER_NAME_IX INDEX 46.5625
INVENTORIES TABLE 80
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 193.25
ITEM_PRODUCT_IX INDEX 195.1875
ORDERENTRY_METADATA TABLE .0625
ORDER_ITEMS_PK INDEX 160
ORDER_PK INDEX 62.1875
ORD_CUSTOMER_IX INDEX 64.3125
ORD_ORDER_DATE_IX INDEX 63.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 61.375
PRD_DESC_PK INDEX .0625
PRODUCT_DESCRIPTIONS TABLE .3125
PRODUCT_INFORMATION TABLE .25
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
WAREHOUSES TABLE .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

34 rows selected.

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

SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1;

SEGMENT_NAME SEGMENT_TYPE SIZE_MB
------------------------------ ------------------ ----------
ADDRESS_CUST_IX INDEX 72.9375
ADDRESS_PK INDEX 64.8125
CARDDETAILS_CUST_IX INDEX 40.875
CARD_DETAILS_PK INDEX 64.875
CUSTOMERS_PK INDEX 42.6875
CUST_ACCOUNT_MANAGER_IX INDEX 29.8125
CUST_DOB_IX INDEX 47.1875
CUST_EMAIL_IX INDEX 64.375
CUST_FUNC_LOWER_NAME_IX INDEX 47.5625
INVENTORIES TABLE 80
INVENTORY_PK INDEX 18
INV_PRODUCT_IX INDEX 15.625
INV_WAREHOUSE_IX INDEX 16.5
ITEM_ORDER_IX INDEX 193.25
ITEM_PRODUCT_IX INDEX 203.1875
ORDERENTRY_METADATA TABLE .0625
ORDER_ITEMS_PK INDEX 160
ORDER_PK INDEX 62.1875
ORD_CUSTOMER_IX INDEX 64.3125
ORD_ORDER_DATE_IX INDEX 72.1875
ORD_SALES_REP_IX INDEX 30.8125
ORD_WAREHOUSE_IX INDEX 64.375
PRD_DESC_PK INDEX .0625
PRODUCT_DESCRIPTIONS TABLE .3125
PRODUCT_INFORMATION TABLE .25
PRODUCT_INFORMATION_PK INDEX .0625
PROD_CATEGORY_IX INDEX .0625
PROD_NAME_IX INDEX .125
PROD_SUPPLIER_IX INDEX .0625
WAREHOUSES TABLE .0625
WAREHOUSES_PK INDEX .0625
WHS_LOCATION_IX INDEX .0625

32 rows selected.

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:

SYS@orcl > select owner, object_name, object_type, created from dba_objects where created > to_date('10/08/2017 05:00','dd/mm/yyyy hh24:mi');

OWNER OBJECT_NAME OBJECT_TYPE CREATED
---------- ------------------------------ ----------------------- ---------
SOE SYS_IOT_TOP_117714 INDEX 10-AUG-17
SOE SYS_JOURNAL_117616 TABLE 10-AUG-17

2 rows selected.

Confirming then the IOT:

SYS@orcl > select index_name, table_name, index_type from dba_indexes where owner='SOE' and index_name like 'SYS%';

INDEX_NAME TABLE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SYS_IOT_TOP_117714 SYS_JOURNAL_117616 IOT - TOP

1 row selected.

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.