B tree Index :

In my previous article i have given the information about the bitmap index with real life examples.In this article i would like to give you information about B tree index with real life examples.User should know that Oracle should create B-tree index by default.B-tree indexes also known as balanced tree indexes.These are most common type of database index. The classic b-tree index structure,there are branches from the top that lead to leaf nodes that contains data.The B-tree structure is from branches to leaf nodes.

Where to use B-tree Indexes ?

So everyone have question in mind that where exactly we need to use the B tree index.The B-tree index is most used index used to improve the performance of any application or any sql query. The main use of B-tree index is to improve the performance of SQL Queries. I have given some examples of bitmap index and instructions about where to use the bitmap indexes. Just like that i would like to give you the information about some guidelines to use B-tree indexes :

1.Primary key tables :

The B-tree indexes are useful when there is primary key for the table.

2.Sorting is needed :

The B-tree index is used when sorting is needed for improving the performance of query.

Real life example :

I would like to give one example of sorting. The sorting is important in some of cases. Lets say Student table has joined with Student_master table and it is joined with Roll_No column.The Roll_No column is not sorted in the table.To apply sorting on that column kindly create b-tree index on that column so as to improve the performance.

Create index SI_Student_Roll_No

on Student(Roll_No);

Create index SI_Student_master_Roll_No

on Student_Master(Roll_No);

These two indexes gives the better performance as the sorting applied properly on it.

3.Range Searches :

The B-tree index is most useful in the range queries or range searches queries.

Real life example :

Lets say user need to fetch the report of Students from Student table where the admission is between 1st Jan 2018 to 31 Jan 2018.So User need to add index on date.

Sub Types of B-Tree Indexes :

In this section i would like to give you different types of B-tree index with some examples.

Type 1 : Index Organized Table :

The index organized tables are different from regular heap organized tables. In Index organized table the rows are stored in an index defined on primary key for that table.The Logical rowid will build the secondary indexes.

Type 2 : Reverse key index :

For some situations The bytes of index key are reversed.Example is 123 is stored as 321.The reverse key index is used to solve the problem of contention for leaf block and improves the performance.

Type 3 : Descending Index :

There are some situations where user needs the data in descending order. For these situations user needs to use B-tree indexes in descending order.The most important indexes used in BI reporting queries as lot of columns from the table in reporting needs descending order data.

Example :

Create index Desc_Student_Roll_No on Student (Roll_No Desc);

Type 4 : Clustered Index :

The another type of index named Clustered index which uses the cluster key for indexing.

These are four sub-types of B-tree indexes. I hope you get the idea about B-tree index in detail.kindly comment in comment section if you like this article or if you have any suggestions.