Types of Index

[Joldnine]

Revise types of index used in databases from the class CS4221, National University of Singapore.

(Data Structure) B+ Tree Index vs RB Tree Index, Hash, Full-text

B+ tree has more nodes in one layer. Less IO for each search.
RB Tree is too deep.

(Space Saving)Sparse vs Dense

Sparse: not all keys will be indexed. Unstable search speed but less space.
Dense: all keys will be indexed. Faster search but more space.

(Physical Storage) Clustered vs Unclustered

Clustered index: rows in the disk are stored in the same order of index, so there can only be one cluster index for the rows.
With a non clustered index there is a second list that has pointers to the physical rows.

(Logical) Primary vs Secondary, Composited

Primary key is unique.
The secondary key can be not unique.