A Guide to the B-Tree Index
A Guide to the B-Tree Index
In this article, I will be explaining what a B-tree index is, how it works, and how you can easily create one in Oracle.
Join the DZone community and get the full member experience.Join For Free
Compliant Database DevOps: Deliver software faster while keeping your data safe. This new whitepaper guides you through 4 key ways Database DevOps supports your data protection strategy. Read free now
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:
- Start at the root node and go to the first level.
- Find the node that covers the range of values that span the value of 109 (for example, a range of 100 to 200).
- Move to the second level from this node.
- Find the node that covers the range of values that span the value of 109 on the second level (for example, 100 to 120).
- Move to the third level from this node.
- 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
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.
Opinions expressed by DZone contributors are their own.