Selecting Materialized Views - Physical Database Design - page 83

Using the materialized view described by Figure 5.5, our three user queries are as
follows:

SELECT job_id, cost, sell, profit
FROM Profit_Fact;

SELECT invoice_date, sum(cost) AS cost, sum(sell) AS
sell, sum(profit) AS profit
FROM Profit_Fact
GROUP BY invoice_date;

SELECT customer_id, sum(cost) AS cost, sum(sell) AS
sell, sum(profit) AS profit
FROM Profit_Fact
GROUP BY customer_id;

All three of these queries scan the Profit_Fact_Table in full.

Query I/O time (sequential scan, shared disk)
= 1,222

× ≈

5.8 ms 7 seconds.

Let's recap again. We'll total up the I/O times for our three queries: profitability by
job, profitability by date, and profitability by customer. We'll figure the numbers for
three different cases: using the base tables, using materialized views specific to the three
queries, and using the common Profit_Fact table.

Disk I/O times using base tables
= (4,780 sec + 2,435 sec + 1,461 sec)/day = 8,676 seconds.

Disk I/O times using Profit_by_Job, Profit_by_Invoice_Date,
and Profit_by_Customer = (233 sec + 185 sec + 185 sec)/day
= 603 sec/day.

Disk I/O time using Profit_Fact table
= creation cost + query frequency

×

I/O time per query
= 188 sec creation cost + (10 profit by job queries/day)
×

(7 sec/query)
+ (5 profit by date queries/day)

×

(7 sec/query)
+ (3 profit by customer queries/day)

×

(7 sec/query)

≈ 314 seconds per day.

Look at this! Using one common materialized view gains more in terms of total
disk I/O than having the three materialized views specifically designed for the given