Learn about what a B-tree index is, how a B-tree index works, and how you can create a B-tree index in Oracle.
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.
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:
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.
To create a b-tree index in Oracle, you use the CREATE INDEX command:
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:
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.
