A data warehouse contains a table 'ORDERS' with columns 'ORDER ID', 'CUSTOMER ID', 'ORDER DATE, and `ORDER TOTAL' You need to optimize a query that frequently retrieves the total order amount for a sp

Question

Options

• ACreate a materialized view that pre-calculates the total order amount for each customer.
• BCreate a view that filters the `ORDERS' table based on the date range.
• Ccreate an index on the 'ORDER DATE column.
• DCluster the ORDERS' table using CUSTOMER ID and `ORDER DATES.
• EIncrease the warehouse size to allow for more compute resources.

• A
  23% (5)
• B
  5% (1)
• D
  59% (13)
• E
  14% (3)

Explanation

Clustering on (CUSTOMER_ID, ORDER_DATE) physically co-locates rows for the same customer in date order on disk, so the query skips directly to the relevant customer's block and scans only the date range needed - eliminating full-table scans and minimizing I/O at the storage level. Option A (materialized view) pre-aggregates totals but can't handle arbitrary date ranges efficiently without recomputing, and it adds maintenance overhead. Option B (regular view) is just a saved query - it stores no data and improves nothing physically. Option C (index on ORDER_DATE alone) ignores the customer filter; a single-column date index still scans across all customers before narrowing by date, making it far less selective. Option E (increase warehouse size) adds brute-force compute but doesn't eliminate unnecessary data reads - it treats the symptom, not the cause.

Memory tip: Think of clustering like a sorted filing cabinet - drawers by customer, folders within each drawer by date. You walk straight to the right drawer and flip to the right folder, instead of searching every file in the cabinet.

No community discussion yet for this question.