Define primary and secondary indices.
Subject Database Management System
NU Year Set: 3.(d) Marks: 5 Year: 2010

A primary index is an index used to organize both the data store and other indexes for more efficient updating and faster access. Only one primary index may be created over any package. However, a single primary index may be created over multiple packages.

The primary index must be one-to-one or one-to-many when accessing records within the data-store. That is, for each access point within the index there can be any number of records that correspond. However, for each record within the data store, there can be, at most, one access point within the primary index. Any type of index except a keyword index may be used as a primary index. All packages within the load should be mapped by a primary index, or none should be.

Proper design of the primary index and packages will:

  • Increase efficiency in the search process.

  • Increase updating efficiency.

  • Decrease memory usage.

Secondary indices are similar to in keys data.table, except for two major differences:

  • It doesn’t physically reorder the entire data the table in RAM. Instead, it only computes the order for the set of columns provided and stores that order vector in an additional attribute called index. 

  • There can be more than one secondary index for a data table. 

Login to post your comment.