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
infrastructure.
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],
online,
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: http://scn.sap.com/docs/DOC-44384
