This tutorial introduces indexes, different types of indexes and demonstrates creation of such indexes and their performance impact. It highlights the best practices for creation of indexes.

Problem Description


With data being distributed across different tables, efficient retrieval of such data is a challenge. Some concerns include :

  1. High query processing time
  2. Increased I/O in full table scans
  3. Repeated sorting of data for each query


Operation


In order to enable efficient data retrieval with minimal table scans hence improving query execution time, indexes are created on the tables.

Solution


An index is a database object created on frequently accessed columns of a table. Indexes contain keys that point to the physical location of where the data is being stored. Indexes are used to:

  1. Improve performance. In most cases, access to data is faster with an index
  2. Ensure uniqueness. A table with a unique index cannot have rows with identical keys


Indexes can be created using one or more columns of a table, providing the basis for both rapid random look ups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table since indexes usually contain only the key-fields according to which the table is to be arranged.

There are different types of indexes that can be created for different purposes :

  1. Unique and Non-Unique Indexes
  2. Composite Indexes
  3. Clustered and Non-clustered Indexes
  4. Bidirectional Indexes
  5. Partitioned and Non Partitioned Indexes