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
