Difference between Primary Index and Secondary Index

Index files are auxiliary file stored in database used in faster accessing of data, and it is important especially when database file size growing exponentially. Today world storage is not a problem, but retrieval/searching on certain criteria is problem.

Index files- Secondary or auxiliary files that help speed up access in primary files

Indexes or access structure: data structure -- data structure used for fast access.
Primary index: An index structure that is defined on the ordering field (that is used to physically order records on disk in sorted file organization)

Secondary index : used to index field that are neither ordering fields nor key fields(there is no assurance that the file is organized on key field or primary key field). One index entry for every tuple in the data file(dense index) contain the value of the indexed attribute and pointer to the block /record

What happens when Key field is used as index filed in Secondary Index ?

• Since the key fields are unique , number of index entries equal to number of records

• Data records need not be sorted on disk

• Fixed length records for index file

What happens when non-Key field is used as index filed in Secondary Index ?

• When a non key filed is indexed, indexed field entry need not be unique, there can be duplicate entry.

How to handles these duplicate entry ?

Three methods are there to handle this duplicate value

1. Duplicate index entries
2. Variable length records (one value should point more than one records)
3. Extra redirection levels