Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I work on Oracle systems where I two type of indices exists in any table:

  1. Unique index (Based on any search key which is unique key).
  2. Normal index (Index made on any search key which is not candidate or primary key).

But now I came to know about two new type of indices from a university documentation:

  1. Primary index
  2. Secondary index

How are primary and secondary indices different from each other? Are they the same as unique and normal index respectively in Oracle?

I also want to know about below two indices:

  1. Dense index
  2. Sparse index

Can we consider dense and sparse indices as type of primary and secondary indices?

share|improve this question

migrated from stackoverflow.com Sep 20 '13 at 11:41

This question came from our site for professional and enthusiast programmers.

    
Unique index and index are keywords for type of index. But primary, secondary , dense and sparse are conceptual types. –  Mani Sep 20 '13 at 9:13

2 Answers 2

Primary index

A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates. Also Called a Clustered index. eg. Employee ID can be Example of it.

Secondary index

A Secondary index is an index that is not a primary index and may have duplicates. eg. Employee name can be example of it. Because Employee name can have similar values.

Dense Index

Index record appears for every search­ key value in the file. Dense indexes point directly to individual records.

Sparse index

contains index records for only some search ­key values. Applicable when records are sequentially ordered on search ­key. Just as with book indexes, sparse database indexes don’t point to individual records, but to ‘pages'

share|improve this answer
1  
In Oracle the primary key is not a "clustered index". In fact you can't create a "clustered index" in Oracle - you need to create an "index organized table". –  a_horse_with_no_name Feb 5 at 9:34

A secondary index is just any index that is not the primary index (of which there can be only one). It can still be unique.

A dense index is an index where every record is contained in the index, even if it does not have a relevant value, whereas a sparse index contains only record with relevant values. I am not sure if this is a meaningful distinction when talking about Oracle database indexes, except that you could say that a normal index is "sparse" in the sense that it does not contain entries where all columns are null. That would make a bitmap index "dense", I suppose.

A primary key index always has to be dense.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.