Difference between primary index, secondary index, unique index and normal index

indexoracle

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

Unique index (Based on any search key which is unique key).
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:

Primary index
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:

Dense index
Sparse index

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

Best Answer

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'

Related Solutions

Sql-server – ny tangible difference between a unique clustered index and a clustered primary key

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.

Oh, and the fact that a table can have one PK and many UK :-).

These are both differences in INTENT not in PERFORMANCE. Otherwise, I don't think there's any difference. Behind any PK or UK the SQL Server builds an index (depending on the request, clustered or not) and the way it's used is transparent for the source is coming from.

Function based index as a conditional unique key

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1 
                      then fk_id 
                      else null 
                   end) );

Best Answer

Related Solutions

Sql-server – ny tangible difference between a unique clustered index and a clustered primary key

Function based index as a conditional unique key

Related Question