Index

An Index is used to enhance the performance of database queries on tables. We can create as many indexes as possible we need or want on a particular table. Please note, in contrast, having more than required indexes can decrease performance as well.

Normally, an index is created when the database starts growing.

Why are indexes needed?

A famous example for better understanding; imagine walking into the Library of Congress and be given the task of finding a specific publication within 10 minutes. Would you be able to complete this task within the given time frame? The Library of Congress is considered the largest library in the world and it houses approximately 170 million items. Now, if you are a smart chap like me 😊, the first thing we would do is ask for access to the library’s index (which can be manual list or a list in a computer system but a list of names and locations of each book in the library) because indexes contain all the necessary information needed to access items quickly and efficiently. In the same manner, a database index contains all the necessary information to access data quickly and efficiently.

Types of Indexes

There are many types of indexes across the board.

• Primary Index
  • Dense Index
  • Sparse Index
• Secondary Index
• Clustered Index
• Non-Clustered Index
• Multi-Level Indexing
• B-Tree Index
• Column Store Index
• Filtered Index
• Hash Index
• Unique Index
• Unique and non-unique indexes
• Clustered and non-clustered indexes
• Partitioned and non-partitioned indexes
• Bidirectional indexes
• Expression based indexes.
• Bitmap Index
• Reverse Index

Advantages of Indexing

• It helps you to reduce the total number of I/O operations needed to retrieve that data, so you don’t need to access a row in the database from an index structure.
• Offers Faster search and retrieval of data to users.
• Indexing also helps you to reduce tablespace as you don’t need to link to a row in a table, as there is no need to store the ROWID in the Index. Thus, you will be able to reduce the tablespace.
• You can’t sort data in the lead nodes as the value of the primary key classifies it.

Disadvantages of Indexing

• To perform the indexing database management system, you need a primary key on the table with a unique value.
• You can’t perform any other indexes in the Database on the Indexed data.
• You are not allowed to partition an index-organized table.
• SQL Indexing Decreases performance in INSERT, DELETE, and UPDATE queries.