Difference between Primary index,secondary index and clusturing index

By | April 26, 2014

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

There are several types of ordered indexes.
1.Primary Index
A Primary Index is specified on the
ordering key field of an ordered file of records. An ordering
key field is used to physically order the file records on disk,and every record has a unique
value for that field.
2.Secondary Index
If the ordering field is not a key field-that is, if numerous records in
the file can have the same value for the ordering field-another type of index, called a
clustering index, can be used.
3.Secondary Index
A file can have at most one physical ordering
field, so it can have at most one primary index or one clustering index, but not both.A
third type of index, called a secondary index, can be specified on any non ordering field of
a file. A file can have several secondary indexes in addition to its primary access method.

Leave a Reply

Your email address will not be published. Required fields are marked *