Existing Index Gets Updated During Online Index Rebuild

Description

In our OLTP production system, a global index of one of our partitioned table – TRANSACTIONS is getting larger and larger with more and more fragmentation.

As a result, the disk space consumed by this index is growing quickly and also influences query operations with a negative impact regarding performance issue.

All in all, this huge index required a rebuild operation by a regular maintain window or an online operation during non-peak time.

When using ALTER INDEX REBUILD ONLINE option, we have to confirm whether the existing index being to be rebuilt is also get updated, which would not lead the existing index into an obsolete status, so that the data accessed by the application is reliable.

Proof of Concept

The following demo test is used to demonstrate the existing index being to be rebuilt will be updated during its rebuilding when using ALTER INDEX REBUILD ONLINE statement.

Preparation

— Objects and data preparation for index rebuild online

SQL> CREATE TABLE T0004 AS SELECT * FROM DBA_OBJECTS;

SQL> CREATE INDEX IDX_T0004_OBJ_ID ON T0004 (OBJECT_ID);

SQL> DECLARE

BEGIN

FOR i IN 1..6

LOOP

INSERT INTO T0004 (SELECT * FROM T0004);

END LOOP;

END;

/

SQL> SELECT COUNT (*), OBJECT_ID FROM T0004 WHERE OBJECT_ID < 5 GROUP BY OBJECT_ID;

COUNT(*)  OBJECT_ID

———- ———-

64          2

64          3

64          4

Steps

Session 1 Session 2 Session 3
Time 1 — For index online rebuild operation

SQL> ALTER INDEX IDX_T0004_OBJ_ID REBUILD ONLINE;
Idle Idle
Time 2 Working… — Update base table simultaneously when index is being rebuild online

SQL> UPDATE T0004 SET OBJECT_ID = OBJECT_ID + 1 WHERE OBJECT_ID = 2;
SQL> COMMIT;
Idle
Time 3 Working… Transaction committed. Idle
Time 4 Working… Idle — Retrieve updated data from the existing index
SQL> SELECT COUNT (*) FROM T0004 WHERE OBJECT_ID = 3;
Time 5 Working… Idle — The result returned by the following SQL statement is ‘128’ before the end of Session 1’s operation

COUNT(*)
———-
128Execution Plan———————————————————-Plan hash value: 447429683

———————————————————————————–

| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————————–

|   0 | SELECT STATEMENT |                  |     1 |    13 |     3   (0)| 00:00:01 |

|   1 | SORT AGGREGATE    |                  |     1 |    13 |            |          |

|* 2 |   INDEX RANGE SCAN| IDX_T0004_OBJ_ID |   128 | 1664 |     3   (0)| 00:00:01 |

———————————————————————————–

Time 6 Index rebuilt successfully. Idle Idle

DB Internal Concerns

Data Consistency of Indexes

How Oracle database server ensures data consistency when rebuilding index online?

When the ONLINE keyword is specified as a part of an ALTER INDEX REBUILD command, a temporary journal table is created to record changes made to the base table.  The journal is an IOT (Index Organized Table) table type. A Row Share Table Lock (RS) is held on the base table during the index rebuild operation.

Oracle database server merges the changes entered in the journal at the end of the index rebuild process.  This merge by which changed rows are incorporated into the new index, is done while the table is still online.

During the index rebuild on line operation, we can use the following SQL commands to check for this.

— Check the new objects created by the index online rebuild operation

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS, OBJECT_ID, DATA_OBJECT_ID

FROM USER_OBJECTS ORDER BY 4;

OBJECT_NAME                    OBJECT_TYPE          STATUS                 OBJECT_ID DATA_OBJECT_ID
------------------------------ -------------------- --------------------- ---------- --------------
MLOG$_T0003                    TABLE                VALID                     893910         893910
REWRITE_TABLE                  TABLE                VALID                     893991         894001
T0001_MV2                      TABLE                VALID                     893996         893996
T0004                          TABLE                VALID                     894443         894443
IDX_T0004_OBJ_ID               INDEX                VALID                     894444         894444
SYS_JOURNAL_894444             TABLE                VALID                     894446
SYS_IOT_TOP_894446             INDEX                VALID                     894447         894447

— Check the object locks when rebuilding the index online

— Object 894446 (SYS_JOURNAL_894444) is holding a Share(S) lock

— Object 894443 (T0004) is holding a Row-S (SS) lock

SQL> SELECT * FROM V$LOCKED_OBJECT;

OBJECT_ID SESSION_ID ORACLE_USE PROCESS                              LOCKED_MODE
---------- ---------- ---------- ------------------------------------ -----------
97        128 BILL       2722                                            3
791        128 BILL       2722                                           3
569        128 BILL       2722                                           3
894000        128 BILL       2722                                        6
894446        128 BILL       2722                                        4
894443        128 BILL       2722                                        2

Furthermore, we can also generate the ASH report while the index online rebuild is still in process. Through the point of the ASH report, the session which is rebuilding the index online is actually creating an index by doing a Full Table Scan against the base table followed by Index Scan on the existing index.

SQL Command Type Distinct SQLIDs % Activity Avg Active Sessions
CREATE INDEX 1 100.00 0.08


Object ID % Activity Event % Event Object Name (Type) Tablespace
894443 8.00 db file scattered read 8.00 BILL.T0004 (TABLE) TBS_DATA
894444 8.00 db file sequential read 8.00 BILL.IDX_T0004_OBJ_ID (INDEX) TBS_DATA

 

Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
128, 339 100.00 CPU + Wait for CPU 80.00 BILL sqlplus@develo…ic.ausregistr 20/300 [ 7%] 4
db file scattered read 8.00 2/300 [ 1%] 0
db file sequential read 8.00 2/300 [ 1%] 2


Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: