In a database, a logical index is an index structure that replaces the usual pointers to physical table blocks with the actual primary key values of the rows in the table. In traditional index structures like B-trees or B+ trees, each index entry typically contains a pointer to the corresponding data block where the indexed row resides. However, in a logical index, these pointers are replaced with the primary key values of the rows.
Logical indexes are always secondary indexes, meaning they are additional indexes created on columns other than the primary key of the table. A primary index is automatically created on the primary key of the table by the database management system (DBMS) itself. So, a logical index is a secondary index that complements the primary index.
To locate a row based on a column value using a logical index, the database performs the following steps:
The database uses the logical index to find the primary key value associated with the column value being searched.
Using the primary key value, the database performs a lookup in the primary index, which directly points to the location of the row in the table where the data resides.
Once the row is found using the primary index, the required column value is retrieved from that row.
A logical index is a type of secondary index where pointers to table blocks are replaced with primary key values. It complements the primary index, which is automatically created on the primary key of the table. To locate a row based on a column value using a logical index, the database first uses the logical index to find the associated primary key value and then uses the primary index to locate the actual row in the table.