Oracle Database Tips by Donald Burleson

B-Tree and Indexes

During any discussion of indexes with Oracle, the subject of B-tree structure is always bandied about as if everyone knew exactly what is being talked about. However, unless you come from a computer science or academic programming background, all you may understand is that a B-tree is a special structure format for an index that allows rapid access of the data in the index. But exactly what is a B-tree? Let's take a quick look at a B-tree structure in Figure 6.1before we go on. Each upper node is known as a branch, while the lowest levels are known as leaf nodes. Branches point to other branches or leaf nodes. Leaf nodes store value-rowid pairs.

The actual distribution will depend on the number of data values in each range of values in a B-tree, with the overall goal to reduce the number of required levels that must be traversed to get to a specific value. The advantages of a B-

tree structure are:

• All leaf blocks are of the same depth (number of values).

• In the case of randomly entered data, the B-tree stays balanced automatically.

• All blocks of a B-tree index are three-quarters full (on the average), allowing insertion without rebuild.

• B-trees provide excellent performance for all types of selects.

• Insert, update, and deletes tend to be efficient in a B-tree structure.

• B-tree performance stays optimal even when tables vary from small to large.

See Code Depot

www.dba-oracle.com/oracle_scripts.htm