A Guide to the B-Tree Index

A b-tree index stands for “balanced tree” and is a type of index that can be created in relational databases.

It’s the most common type of index that I’ve seen in Oracle databases, and it’s the default index type. By this, I mean that if you don’t add any modifiers to your CREATE INDEX statement, then a b-tree index will be created.

How Does a B-Tree Index Work?

A b-tree index works by creating a series of nodes in a hierarchy. It’s often compared to a tree, which has a root, several branches, and many leaves.

In my definitive guide on indexes in Oracle, I use an example of finding a record with an ID of 109. 

The steps to find this record would be:

1. Start at the root node and go to the first level.
2. Find the node that covers the range of values that span the value of 109 (for example, a range of 100 to 200).
3. Move to the second level from this node.
4. Find the node that covers the range of values that span the value of 109 on the second level (for example, 100 to 120).
5. Move to the third level from this node.
6. Find the record that has an ID of 109.

There’s a set of looking up a range of values and stepping to the next level. This is repeated a few times until the correct row is found.

How Can I Create a B-Tree Index?

To create a b-tree index in Oracle, you use the CREATE INDEX command:

CREATE INDEX index_name ON table_name (columns) 

When you write a CREATE INDEX statement, you don’t need to specify the type of index if you’re creating a b-tree index. By default, the index created is a b-tree index.

To complete this statement you need to specify:

• index_name : This is the name of the index you’re creating.
• table_name: This is the name of the table that the index is created on.
• columns: This is a comma-separated list of the columns that the index is being created on. You can specify one column or many columns.

What Should I Name My Index?

Every index you create needs a name, and the name must be unique.

Another thing I’ve mentioned before in the guide to indexes and on my site, is how I like to name my indexes.

The format I use is ix_table_columns.

I prefer to add a prefix to indicate it is an index, such as ix. Then I have an underscore, then the name of the table. Then another underscore, then the columns it refers to.

Now the index name needs to be short, so I come up with shorter names for the tables in this index name.

An example would be ix_emp_fname. This would refer to an index on the employee table, on the first_name column.

So, that’s what a b-tree index is. They are pretty easy to create, efficient in finding data, but not always the solution to your query performance problems.