Tanubhrt.This.Side

In the last post of the series, we learnt what indexes are, the introduction of the types. This post is about single ordered indexes.

While defining the index, we usually pick one attribute for indexing instead of multiple. This attribute or field is called the indexing attribute or indexing field. The index typically stores each value of the index field along with a list of pointers to al disk blocks that contain records with that field value. So, in simpler terms, if we have a table where the index is done on the country name, then all the country names along with the pointers is maintained in another table. So, if there are 10000 records for India in the main table, there is a single entry made in the index table, with the country name and the list of all the pointers to the main table. The values in the index table are ordered so that we can do binary search on the index. In the case of multi-level indexing, there is no need for binary search since it is replaced by creating indexes of the index itself.

When we consider the ordering indexes, there are several types of ordered indexes.

1. Primary Index – Often when maintaining the data in our tables/files, we also add a column which maintains the order of the data. As per Navathe, a primary index is specified on the ordering key field of an ordered file of records.
2. Clustering Index – If the ordering field of the file/table is not unique and has multiple values, we go for clustering index.

A file can have at most one physical ordering field, so it can have at most one primary index or one clustering index but not both.

3. Secondary Index – This type of index is specified on any non-ordering field of a file. A file can have several secondary indexes in addition to a single primary index.

Let us now try to grasp the above concepts via the method of questions

What does an index stores?

An index typically stores each value of the index field along with a list of pointers to all disk blocks that contain records with the field value.

Why are values in the index ordered?

The value in the index are ordered so that we can do binary search on the the index.

What are the types of ordered index?

Primary index, clustered index, secondary index.

What is a primary index?

A primary index is the one specified on ordering key field of an ordered file of records.

What is a clustered index?

A clustered index is defined on the field which has multiple records for the ordering field.

What is a secondary index?

A secondary index is the one specified on the non ordering field of the file.

What is the difference between primary, secondary and clustered index?

Primary index is the one specified on the ordering field having unique values, while clustered index is specified on the ordering field having multiple values. Secondary index is specified on non-ordering field.

In the next post, we will discuss in detail about the primary index.

Happy Learning 🙂