Internals Of Index Online Building

Background:

Whenever a session seems stuck or doesn’t comes out many DBA’s /Developers have a habit to cancel their session (CTRL C) or kill the session. My bad, I did the same when I was testing rebuilding of an index.

Sql_Sesssion1> Select object_name,object_id,object_type From user_objects;

OBJECT_NAME                                OBJECT_ID                           OBJECT_TYPE

-----------------------------------------------------------------------------------------------------------------------------------------------------

IDX                                                        61837                                         INDEX

ALL_OBJ                                              61836                                        TABLE

In session 2:

Sql_Sesssion2> Alter index idx rebuild online;

CTRL+C <------

And I got this

ORA-01013 user requested cancel of current operation.

But when I checked my 1st session again with same query I got more number of objects present in my schema (Journal table and IOT index). 

Sql_Sesssion1> Select object_name, object_id, object_type From user_objects;

OBJECT_NAME                                                      OBJECT_ID                           OBJECT_TYPE

--------------------------------------------------------------------------------------------------------------------------------------

IDX                                                                               61837                                                INDEX

ALL_OBJ                                                                     61836                                              TABLE

SYS_IOT_TOP_61851                                               61852                                               INDEX

SYS_JOURNAL_61837                                             61851                                              TABLE

Wondering why these objects get created???

So this post is of mine is intended to describe online index rebuilding internals and a brief of journal table.

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the rebuild is in progress and merge all the changes from journal table to complete index build operation.

Journal table is the index organized table and their name will essentially be SYS_JOURNAL_<index object_id>. It gets dropped automatically when the job completes successfully or cleans or cancelled successfully.

Refer above query, IDX object_id is 61837 and Journal table named as SYS_JOURNAL_61837

The structure of Journal table looks like

Sql_Sesssion1> DESC  SYS_JOURNAL_61837

Name                                  Null?                  Type

 ----------------------------------------------------------------------------------------------------------- 

 C0                                    NOT NULL         NUMBER

 C1                                    NOT NULL         NUMBER

 OPCODE                                                     CHAR(1)

 PARTNO                                                     NUMBER

 RID                          NOT NULL         ROWID

Where

"OPCODE" column represents the type of operation like "I" for Insert and "D" for Delete. Any update operation of index key columns would be converted to "DELETE" and "INSERT" in the journal table.

"PARTNO" column represents partition number of the underlying table.

Internal Processing:

OIB will get in the DML queue to lock the table exclusively while preventing the new DML's to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it'll still keep the share lock on the table to prevent any other DDL's) for DML's to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add "ROWID" to that list to make it as primary key.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. 

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, its reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML's again to do the final merge and drop the journal table before releasing the mode 6 exclusive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML's happen while Oracle is doing the merge, it'll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

Please Note: In 11g, major changes were introduced to handle online index rebuilding. The above post is meant for previous version to 11g.

If you find this article interesting then please provide your feedback through comments.