CompTIA DataSys+ (DS0-001)Database DeploymentHard
A database administrator is planning the storage for a new database that will host a real-time analytics application. The application frequently performs complex aggregations and joins across very large datasets, where query performance is paramount, but data updates are infrequent. Which database indexing strategy would be most beneficial for this workload?
- ABitmap indexes on low-cardinality columns
- BFull-text indexes for search capabilities
- CB-tree indexes on primary keys
- DHash indexes on frequently accessed columns
Show answer & explanationAnswer & explanation
Correct answer: A. Bitmap indexes on low-cardinality columns
Bitmap indexes are highly effective for data warehousing and analytical workloads involving complex queries with aggregations and joins on large datasets, especially when dealing with low-cardinality columns (columns with a limited number of distinct values). They allow for efficient combination of multiple conditions, significantly speeding up query performance.
Why the other options are wrong
- B. Full-text indexes are specialized for text search and are not relevant for improving performance on numerical or categorical data aggregations and joins.
- C. B-tree indexes are general-purpose and excellent for primary keys and range queries, but might not be optimal for complex aggregations across many low-cardinality columns in a data warehouse context.
- D. Hash indexes are good for equality lookups but less effective for range queries, aggregations, or complex join conditions typical of analytical workloads.
Database Indexing Strategies
Techniques for optimizing database query performance by creating data structures that allow for faster retrieval of records based on column values.
- B-tree indexes are good for range queries and equality checks on high-cardinality data.
- Hash indexes are efficient for equality lookups but not for range queries.
- Bitmap indexes are ideal for data warehousing on low-cardinality columns, enabling efficient combination of multiple conditions.
- Full-text indexes are for searching text data.
Memory trick: Index for access patterns: B-tree for ranges, hash for equality, bitmap for analytics.