Question: What is the difference between a
"rebuild index" and the "rebuild index online" operation. When
do I consider adding the "online" argument to a rebuild index
statement?
Answer: The fundamental difference between an
"alter index rebuild" and an "alter index rebuild online" is the
access methods for the index data.
Online index rebuild and locking
With a standard "alter index rebuild" Oracle will set an exclusive
lock, effectively blocking all DML that might happen during the
rebuild period. However, this is mitigated with Oracle parallel
index rebuilding. An index that takes an hour to rebuild can
be completed in less than one minute on a server with cpu_count=64
and parallel degree 63.
With the online index rebuild, update transactions will still be able to access
the table and index. Only for very small amounts of time is a lock
acquired on the target table.
In 11g and beyond, Oracle will wait for any long running DML
transactions to complete before starting the online index rebuild,
but it will not block any new DML activity once the online rebuild
has started.
As soon as the online index rebuild operation has begun, Oracle
will create an IOT journal table and use an internal trigger on the
underlying table to keep track of the DML changes that occur during
the index rebuild operation. After the rebuild operation has
completed, Oracle will apply all DML changes to the target index.
Differences between online and standard
index rebuild
Here is a summary of the differences between index rebuilding
techniques:
- Alter index rebuild online: During a
online index rebuild, Oracle will make a snapshot log on the
target table to hold DML activity, read the table in a
full-table scan (read consistent), build the new index and then
apply the changes from the snapshot log after the index has been
rebuilt. You can use NOLOGGING and PARALLEL with the online rebuild:
alter index my_idx rebuild online parallel 63 nologging;
alter index my_idx noparallel;
alter index my_idx logging;
- Alter index rebuild: During a
"regular" index rebuild, an exclusive lock occurs as the existing index is
read. Hence, this command is designed for scheduled
downtime periods where there is no DML activity. However,
this operation can be parallelized and run in NOLOGGING mode:
alter index my_idx rebuild parallel
63 nologging;
alter index my_idx noparallel;
alter index my_idx logging;
Note: In some cases the
specification of the "parallel" clause in an index rebuild will
change the default degree of parallelism for the index. Hence,
you will always want to set the index back to "noparallel" after
completion of the rebuild.
The rules for identification of candidates for index
coalescing/rebuilding depend on your specific index state. See MOSC
notes 989186.1, 122008.1, 989093.1 for Oracle's suggestions on when
to coalesce/rebuild indexes. Also see my
updated notes on index coalesce or rebuilding and note this
demonstration of an index that benefits from
scheduled oracle index rebuilding.
Also, please see my
updates and
other notes on index rebuilding strategies, and my complete
notes are found in my book "Oracle
Tuning: The Definitive Reference".
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|