Secondary Indexes

A secondary index provides a secondary means (other than the primary key) to access table data. Unlike primary indexes, secondary indexes aren't necessarily unique and aren't based on the primary key of a table. The records in a secondary index are stored separately from the data, and each index entry points to the corresponding data record. A table can have multiple secondary indexes.

Characteristics of a :

1. Provides an alternative path to access the data.
2. Can be either dense or sparse.
3. Allows for non-unique values.
4. Does not guarantee the order of records in the data file.
5. Unlike a primary (often clustered) index, a secondary index is typically a non-clustered index. This means the physical order of rows in a table is not the same as the index order.

Secondary Index Example

Let's continue with the `Students` table:

| RollNumber (Primary Key) | Name  | Age |
|--------------------------|-------|-----|
| 1001                     | John  | 20  |
| 1003                     | Alice | 22  |
| 1007                     | Bob   | 21  |
| 1010                     | Clara | 23  |

Assuming we want to create a secondary index on the `Age` column:

Dense Secondary Index on Age:

| Age | Pointer to Record |
|-----|-------------------|
| 20  | Record 1          |
| 21  | Record 3          |
| 22  | Record 2          |
| 23  | Record 4          |

Here, each age value has a direct pointer to the corresponding record.

If another student with an age of 22 is added:

| RollNumber | Name  | Age |
|------------|-------|-----|
| 1012       | David | 22  |

The dense secondary index would then be:

| Age | Pointer to Record |
|-----|-------------------|
| 20  | Record 1          |
| 21  | Record 3          |
| 22  | Record 2, Record 5|
| 23  | Record 4          |

Benefits of a Secondary Index:

• Provides additional query paths, potentially speeding up query performance.
• Can be created on non-primary key columns, even on columns with non-unique values.
• Useful for optimizing specific query patterns.

Drawbacks of a Secondary Index:

• Increases the overhead of write operations since any insert, update, or delete operation on the table may require changes to the secondary index.
• Consumes additional storage space.
• May increase the complexity of database maintenance.

In a real-world scenario, database administrators and developers need to strike a balance. They need to identify which columns are frequently used in query conditions and might benefit from indexing, while also considering the trade-offs regarding space and write operation performance.