CompTIA DataSys+ (DS0-001)Database DeploymentHard
A database administrator is deploying a new database server for a financial application that requires extremely low latency for complex analytical queries involving multiple joins across large datasets. The primary concern is query performance, and the data is mostly read-intensive. Which type of database index would be MOST beneficial for this scenario?
- AHash Index
- BNon-clustered Index
- CBitmap Index
- DClustered Index
Show answer & explanationAnswer & explanation
Correct answer: C. Bitmap Index
Bitmap indexes are highly effective for data warehousing or decision support systems with low cardinality columns (few distinct values) and complex queries involving multiple WHERE clauses and joins, as they can quickly combine results from multiple indexes.
Why the other options are wrong
- A. Hash indexes are primarily used for equality lookups and are not suitable for range queries, sorting, or combining multiple conditions in complex analytical queries.
- B. Non-clustered indexes provide fast lookup for specific columns but can be less efficient than bitmap indexes for combining results from multiple columns in complex analytical queries.
- D. Clustered indexes define the physical order of data and are good for range scans, but less effective for combining multiple conditions on different columns in complex analytical queries.
Bitmap Index
A database index type that uses bitmaps (binary arrays) to efficiently store and query data, particularly effective for columns with low cardinality in data warehousing environments.
- Excellent for complex analytical queries with multiple predicates (AND/OR).
- Best suited for columns with a small number of distinct values (low cardinality).
- Less efficient for high-cardinality columns or transactional workloads (high update rates).
Memory trick: Bitmap: Bits for Big Analytics.