Databases Reference
In-Depth Information
The rebuild process is not like a
DROP
and
CREATE
sequence of operations. When an index
is created, the table is completely scanned to build the index, while a
REBUILD
recalculates
the
ROWID
for the index entries, which are already in the index, generating the new segments.
At the end of the process, the old segments of the index are dropped and replaced by the
new ones. The entries for the DML operations happened while the index was rebuilt, and are
added/updated to the new index. These DML operations were recorded in a
journal
table
,
which is itself an index organized table. When all the operations are completed, information
about the index on the data dictionary is updated, and only during dictionary updates is DML
access blocked.
We have used the
PARALLEL
option too, to speed up the rebuild process.
As we have experimented, during an online index rebuild, we can execute DMLs on the base
table, but take care as the operation will be slower than usual.
Normally, there is no need to rebuild an index if the status of the indexes is
VALID
. However,
when we have a table on which there are many INSERTs and DELETEs, we could schedule an
index rebuild, because when deleting an index entry, the space is not freed in the index leaf,
but just marked as deleted. If we have massive
DELETE
and
INSERT
operations, we could
have a skewed index structure, which could slow performance due to intra-block fragmentation.
In such cases, an index rebuild can be helpful, and the
ONLINE
options allow us to perform
this operation without affecting the availability of the database.
We can check if an index needs to be rebuilt by executing the following statements:
ANALYZE INDEX index_name VALIDATE STRUCTURE;
SELECT HEIGHT, DEL_LF_ROWS, LF_ROWS, LF_BLKS FROM INDEX_STATS;
If the value for
DEL_LF_ROWS/LF_ROWS
is greater than 2, or
LF_ROWS
is lower than
LF_BLKS
, or
HEIGHT
is 4 then the index should be rebuilt.
Index rebuild and statistics
When we rebuild an index, we can add the
COMPUTE STATISTICS
option to the statement.
With the index, even the statistics on it are rebuilt, with a minor effect on the performance of
the operation.
See also
F
We will talk about data loading and direct path load in
Chapter 8
, in the recipes
Direct path inserting
and
Loading data with SQL loader and Data Pump
