There are two common types of indexes in Oracle SQL. Let’s take a look at what they are and how they are different from each other.
What is an index?
If you’re not sure what an index is, let’s cover it briefly.
An index is an object in an SQL database that allows queries to run faster. They are created to refer to a single table and one or more columns, and there are several types of indexes. Two of the more common types of indexes are b-tree indexes and bitmap indexes.
Indexes are most often created to improve the performance of SELECT queries, as they improve the speed of getting data from the database. They can slow down INSERT, UPDATE, and DELETE queries, depending on the query.
There’s a balancing act between creating no indexes, and creating too many indexes which will slow down all of your queries. Getting this right takes knowledge of your database, experience with SQL, and sometimes a little experimentation.
What is a B-Tree Index?
A b-tree index, which is short for “balanced tree index”, is a common type of index. It’s the default index created in a CREATE INDEX command if you don’t specify any index type.
It works by creating a tree-like structure for an index, where a root node exists and there are branches created from this root node.
Oracle will use this structure like a series of choices for a path, to find the value that’s needed. Each branch on the path will further narrow the range of values that Oracle needs to see, until the value is found.
B-tree indexes are good for columns where there is a high number of different values. This could be for a primary key, a customer name, or a foreign key.
What is a Bitmap Index?
A bitmap index is another type of index, and it’s also quite popular.
It allows an index to be represented as a two-dimensional structure, with one of the dimensions the ROWIDs for rows in a table, and the other dimension is the different values in a column. The intersection of the rows and columns is a boolean value, which indicates if that row has that value.
So, how does this help you in your queries?
Any query that filters by a column value that has a bitmap index could run faster with this index applied. It is better suited to different types of columns that the b-tree index. I’ve recently created a guide to indexes in Oracle, which includes the detailed breakdown of the syntax, but there are only a few differences between them.
So how are they different?
Difference between B-tree and Bitmap index
Here are some of the differences between these two index types:
- B-tree indexes are the default index type of the CREATE INDEX statement, but to create a bitmap index you need to specify CREATE BITMAP INDEX.
- B-tree indexes are suitable for columns with a high number of distinct values. Bitmap indexes are suitable for columns with a low number of distinct values.
Those are the two main differences between the index types.
If you’re wondering which type of index to create, just look at the number of distinct records in your table.
You can do this by using a SELECT DISTINCT query, or SELECT VALUE, COUNT(*) with a GROUP BY.
I can’t really suggest a specific number as a limit, and say anything under that number should use a bitmap index and everything over that number should use a b-tree index, because it depends on your data and database. But you can use your own judgement and experience.
So, there’s an explanation of these two index types and some of the differences between them.
Hope you like the article. We highly appreciates comments and feedback.
Ben has over 10 years experience working in the software industry, and he enjoys teaching Oracle and database skills to other software developers.