Primary index sparse index

Primary index - Sparse index





Go back

Primary index dense index

Primary index - Dense index / What is dense index? / Define Dense index / Dense index example

Dense Index

Index entry appears for every search key value in the record file (data file). In other words, “one entry in the index file for every record that is stored in the data file”.

For example, consider the figure (primary index) given below. You may find all the register numbers that are part of the data file as search key entries in the index file. This type of primary index is called as dense index. 

Primary index - Dense index

Points to remember about dense indexes:

• Every value of the key attribute of the data file has a representative entry in the appropriate dense index file. Hence the number of entries in the index file is equal to the number of records in the data file.

• The index maintains the key values in the same order as in the data file.

• It supports point queries, and range queries (if the look up attribute is the indexed attribute).

 






Go back

Secondary index

Secondary index / Secondary indexing / Non-clustering index / Non-clustered index

Secondary index

An index whose order of entries is different from the order of the data file is called as secondary index.

Another definition

A non-clustering, non-clustered, or secondary index specifies an order that is different from the file/table/relation’s sequential order.

Example

In this example, the left side table is the index table, and the right side is the Students table (Data file). The search key or the ordering key field in the index table is the Name of the students, which is neither primary key nor unique attribute of the students table. Name attribute is just an attribute in the students table. In our example, index is sorted on Name attribute, and Data file is sorted on regno attribute.

Secondary index - example

Points to know about secondary index:

• Secondary index must be a dense index

• Order of the search key values in the index file and the order of the actual records that are related to the index entries are in different order.

• Secondary Indexes facilitate query-answering on attributes other than primary keys – or, more generally, on non-ordering attributes.

• A file can have several secondary indexes.

 

Go back

Primary index

Primary index / Primary indexing / Clustered index / Clustering index / index sequential file

An index whose order of the entries is same as the order of the records in the database table file is called as primary index. It can also be referred as clustering index, or clustered index.

Primary or clustering index’s search key is often the primary key of the table. It is not compulsory to have primary key attribute as the primary index’s search key. We can have other attributes as well.

Points to know about primary index:

• A Primary Index forces a sequential file organization on the Data File;

• Since a Data File can have just one order there can be just one Primary Index for Data File

• The combination of a sequentially ordered file plus a clustering index is an index-sequential file

Primary index example:

In this example, the left side table is the index table, and the right side is the Students table. The search key or the ordering key field in the index table is the register number of students, which is the primary key of the students table. Observe from the picture given below the order of both index and the data file are the same.

Primary index (dense) example

A primary index (clustering index or clustered index or index on sequential files) can be one of two of the following types;

• Dense index

• Sparse index

 


Go back