CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database administrator is designing a new database for a retail company. The company frequently needs to generate reports showing the total sales for each product category on a daily basis. To optimize query performance for these reports without storing redundant data, which database object should the administrator implement?
- AStored procedure
- BTrigger
- CIndex
- DMaterialized view
Show answer & explanationAnswer & explanation
Correct answer: D. Materialized view
A materialized view pre-computes and stores the results of a query, which is ideal for frequently accessed, complex aggregations like daily sales reports. This improves query performance significantly compared to recalculating the results each time.
Why the other options are wrong
- A. A stored procedure is a set of SQL statements, useful for encapsulating logic but doesn't pre-compute data.
- B. A trigger is a special type of stored procedure that runs automatically when an event occurs on a table.
- C. An index speeds up data retrieval on specific columns but doesn't store aggregated query results.
Materialized View
A database object that contains the results of a query, physically stored in the database, and periodically refreshed to reflect changes in the base tables.
- Improves query performance for complex aggregations.
- Consumes storage space as it stores data.
- Requires refresh mechanisms to keep data up-to-date.
Memory trick: Materialized views are like pre-cooked meals for your queries.