Oracle Indexes – Is Maintenance Required?
The question about whether Oracle indexes are self-balancing is
largely a matter of semantics. As rows are added to an empty table,
Oracle controls the addition of same level blocks, called splitting,
until the higher level index node is unable to hold any more key
pointer pairs. When the index can no longer split because the owner
block is full, Oracle will spawn a whole new index level, keeping
the index tree in perfect logical and physical balance.
However, DELETEs are a different story. Physically, Oracle indexes
are always balanced because empty blocks stay inside the tree
structure after a massive DELETE. Logically, Oracle indexes are not
self-balancing because Oracle does not remove the dead blocks as
they become empty. For example, Figure 15.10 shows an Oracle index
before a massive delete.
Figure 15.10:
A
physical index before a massive row delete
Now, after a massive delete, the physical representation of the
index is exactly the same because the empty data blocks remain as
illustrated in Figure 15.11. However, the logical internal pointer
structure is quite unbalanced, because Oracle has routed around the
deleted leaf nodes and has placed the empty index blocks back on the
freelist, where they can be reused anywhere in the index tree
structure
Figure 15.11:
The logical
pointer structure of an index after a massive row delete
This type of sparse index is typical of an index on highly active
tables with large scale INSERTs, DELETEs and UPDATEs. There may be
thousands of empty or near empty index blocks, and the sparse data
can cause excessive I/O. There are several types of Oracle
execution steps that will run longer on this type of sparse index:
§
Index range scans that must access many near empty blocks will have
excessive I/O compared to a rebuilt index.
§
Because 70% of an index can be deleted and the index
will still have the same number of data blocks, a full index scan
might run many times slower before it is rebuilt.
Since the SQL must visit the sparse blocks, the task will take
longer to execute.
SEE CODE DEPOT FOR FULL SCRIPTS
