Skip to Content
Author's profile photo Former Member

Online Utilities for Uninterrupted Access to Data

Data explosion is one of the common problems in most of the enterprises. The increase in the volumes

of the data will increase the length of the maintenance window. Maintenance windows are used to

improve the data quality by executing utilities that reorganize the data or recreate the indexes on the

table for better response time. Duration of the utility depends on the volume of the data in the table. 

Huge volumes of data will lead to longer maintenance windows.


The current business trends can not afford the long maintenance windows and they force 24 X 7 data

availability. If utilities such as Data Reorganization or Create Index take exclusive lock on the table, it directly

conflicts with requirement of keeping the data available almost all the time.  This challenge leads to a need to

change these utilities in such a way that they don’t block the access to the data all the time and still maintain the

integrity of the data or make these utilities operate in online mode.

This business request leads to an interesting set of utilities called Online Utilities, which we achieved using 3-Phase


3-Phase infrastructure breaks the utility in 3 parts,

a) Setup: is a short period blocking phase, independent from data volume.

b) Data Copy: is a non blocking phase and responsible for most of the work done for reorganization of data.

c) Synchronization: is last phase which actually synchronized the DMLs came in during data copy phase.

Syntax Change:


The option ‘online’ will be added to the current create index / Reorg syntax:


Create Index:


create [unique] [clustered | nonclustered] index index_name

        on [[database.]owner.]table_name

               (column_expression [asc | desc]

                   [, column_expression [asc | desc]]…)

      [with { fillfactor = pct,

                   max_rows_per_page = num_rows,

                   reservepagegap = num_pages,

                   consumers = x, ignore_dup_key, sorted_data,

                    [ignore_dup_row | allow_dup_row],


                   statistics using num_steps values  } ]

      [on segment_name]

      [index_partition_clause ]

Reorg Rebuild:


reorg rebuild table_name

      [index_name [partition index_partition_name]]

      [with online]

For more detail please refer:

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.