Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 531

Root Block

<1000 <4000

Branch Block

<1000 <2000 <3000 <4000

Leaf Block

Key:1324 Key:1325 Key:1326 Key:1327

Figure 15-5. Index leaf blocks for B-tree index

the root block contains data that points to the branch block, which in turn points to the lower level leaf block.
the lowest level contains the indexed data values and corresponding ROWIDs required for locating the row.

b-tree indexes provide considerable performance benefits where the index key value is unique across the
database. performance degradation of the b-tree index can be noticed when the index key value is non-unique
and the number of rows per value gets higher.

b-tree indexes are useful in a raC environment provided there is no collision of index key values, and the inserts
into the leaf blocks are not adjacent to each other. if the values being inserted are sequential in nature, which
means they are most likely to be inserted into the same leaf block, performance degradation occurs due to
frequent index leaf block splits. under such situations, it would be advisable to use the reverse key index option.

Various index types carry different structures. the b-tree structure has the following advantages:

· all leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the

index takes approximately the same amount of time. it is important that the index tree does not
get very deep; and hence, a tree structure that is more than four levels deep would be inefficient.

· b-tree indexes automatically stay balanced.

· all blocks of the b-tree are three-quarters full on the average.

· b-trees provide excellent retrieval performance for a wide range of queries, including exact

match and range searches.

Step 10
Monotonously increasing surrogate keys can cause high amounts of contention when leaf blocks splits occur and
when the blocks need to be transferred over the interconnect, for new rows need to be inserted from other instances
in the cluster.
Figure 15-5 illustrates the branch block and leaf block structure. Every time a new row is inserted, it will be
stored in the rightmost leaf block of the index. As more and more rows are inserted into the table, this will cause
the rightmost leaf block to fill and will cause the leaf block to split into two leaf blocks. The contention in a RAC