Interview Questions and Answers

Explain Index Depth, Density and Selectivity factors and how these factors affect index performance?

Index depth is the number of levels from the index root node to the leaf nodes. An index that is quite deep will suffer from performance degradation problem. In contrast, an index with a large number of nodes in each level can produce a very flat index structure. An index with only 3 to 4 levels is very common.
Index density is a measure of the lack of uniqueness of the data in a table. A dense column is one that has a high number of duplicates.
Index selectivity is a measure of how many rows scanned compared to the total number of rows. An index with high selectivity means a small number of rows scanned when related to the total number of rows.

Index depth is the number of levels from the index root node to the leaf nodes. An index that is quite deep will suffer from performance degradation problem. In contrast, an index with a large number of nodes in each level can produce a very flat index structure. An index with only 3 to 4 levels is very common.
Index density is a measure of the lack of uniqueness of the data in a table. A dense column is one that has a high number of duplicates.
Index selectivity is a measure of how many rows scanned compared to the total number of rows. An index with high selectivity means a small number of rows scanned when related to the total number of rows.

SQL Talk