Oracle Index Internals

ZT from

http://hi.baidu.com/dbaeyes/blog/item/5bd7c31c3c63838087d6b616.html

[@more@]摘錄自Oracle Index Internals
Classic Oracle Index Myths
Oracle B-tree indexes can become “unbalanced” over time and need to be rebuilt
Deleted space in an index is “deadwood” and over time requires the index to be rebuilt
If an index reaches “x” number of levels, it becomes inefficient and requires the index to be rebuilt
If an index has a poor clustering factor, the index needs to be rebuilt
To improve performance, indexes need to be regularly rebuilt

Index Fundamentals
Oracle’s implements a form of B*Tree index
Oracle’s B-Tree index is always balanced. Always.
Index entries must always be ordered.
An update consists of a delete and an insert
Each leaf block has pointers to next/previous blocks
Each leaf block contains the index entry with corresponding rowid
Index scans use ‘sequential’, single block reads(with exception of Fast Full Index Scan)


Index Rebuilds
? As discussed, most indexes are efficient at allocating and reusing space
? Randomly inserted indexes operate at average 25% free space
? Monotonically increasing indexes operate at close to 0% free space
? Deleted space is generally reusable
? Only in specific scenarios could unused space be expected to be higher and remain unusable
? So when may index rebuilds be necessary ?

Conditions for Rebuilds
Large free space (generally 50%+), which indexes rarely reach, and
Large selectivity, which most index accesses never reach, and
Response times are adversely affected, which rarely are.
Note requirement of some free space anyways to avoid insert and subsequent free space issues
Benefit of rebuild based on various dependencies which include:
– Size of index
– Clustering Factor
– Caching characteristics
– Frequency of index accesses
– Selectivity (cardinality) of index accesses
– Range of selectivity (random or specific range)
– Efficiency of dependent SQL
– Fragmentation characteristics (does it effect portion of index frequently used)
– I/O characteristics of index (serve contention or I/O bottlenecks)
– The list goes on and on ….

Other Rebuild Issues To Consider Other Rebuild Issues To Consider
More efficient index structures can reduce stress on buffer cache. Harder to formulate but requires consideration
If you have the resources and you have the appropriate maintenance window, then the cost vs. benefit equation more favorable to rebuild
– Benefit maybe low but perhaps so is the relative cost
Rebuild or Coalesce ?

Index Coalesce
More efficient, less resource intensive, less
locking issues than rebuild option
Can significantly reduce number of leaf blocks in
some scenarios
Requires sum of free space to exceed 50% +
pctfree in consecutive leaf blocks
However, as generally needs excessive 50%+
freespace for rebuild to be effective
Does not reduce index height
alter index bowie_idx coalesce;

Summary
The vast majority of indexes do not require rebuilding
Oracle B-tree indexes can become “unbalanced” and need to be rebuilt is a myth
Deleted space in an index is “deadwood” and over time requires the index to be rebuilt is a myth
If an index reaches “x” number of levels, it becomes inefficient and requires the index to be rebuilt is a myth
If an index has a poor clustering factor, the index needs to be rebuilt is a myth
To improve performance, indexes need to be regularly rebuilt is a myth

Cost =
BLevel +
Index Selective × Leaf Blocks
[ + Table Selective × Clustering Factor]
[ - 1]