Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

A Guide to the B-Tree Index

DZone's Guide to

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.

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

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.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

Topics:
sql ,database ,oracle ,tutorial ,b tree ,indexes

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}