Which index type is best suited for analytics workloads with large scans?

• A. B-tree
• B. Bitmap
• C. Columnstore
• D. Hash

Answer: (C)

For analytics workloads with large scans, a columnstore index is the best choice. It stores data column by column, so queries that read many rows but only a few columns can skip a lot of data and read just the needed columns. This reduces I/O and, with high compression, minimizes the amount of data that must be moved. Columnstore also enables batch mode processing and vectorized execution, which speeds up aggregations over large datasets.

In contrast, row-oriented indexes like B-trees are great for point lookups and range queries on individual rows but become inefficient for wide scans across many columns. Bitmap indexes help with certain filters on low-cardinality columns but don’t scale well for large scans on high-cardinality data. Hash indexes are designed for fast exact lookups and joins, not for scanning large analytic workloads.