Selecting Materialized Views - Physical Database Design - page 82

Again, we see a savings in disk I/O resulting from utilizing a materialized view to
answer repeated queries. At this point, one might think, “I'll just keep going, and create
a materialized view for each of the common queries and make a huge net gain!” How-
ever, we do need to live within our resource constraints. There are a number of con-
straints discussed in Section 5.4. Suffice it to say here, we need to keep the number of
materialized views under control. Let's examine the possibility of combining some
materialized views.
If you'll look back at Figures 5.2, 5.3, and 5.4, you'll notice that they all have cost,
sell, and profit in common. It is reasonable to consider combining these tables, since
they are very similar. Figure 5.5 specifies the schema for the combined table. The job_id
is the primary key, since we have this functional dependency: job_id → invoice_date,

customer_id, cost, sell, and profit.
The query to generate the data for the Profit_Fact table is the following:

SELECT c.job_id, i.invoice_date, i.customer_id,
sum(c.cost) AS cost, sum(li.sell) AS sell, sum(li.sell)
- sum(c.cost) AS profit
FROM Invoice AS i, Job_Costing AS c, Line_Item AS li
WHERE i.invoice_id = li.invoice_id
AND c.job_id = li.job_id
GROUP BY i.customer_id;

The I/O cost to create the Profit_Fact table is equal to the cost to query the base
tables plus the cost to write the result to disk. The query against the base tables leads to
the same joins that we calculated for the Profit_by_Customer table.

× ≈

Query I/O time (dedicated disk) = 84,008 2.2 ms 185 seconds.

The number of rows is equal to the number of jobs, since the job_id is the primary
key. Thus, there are 2,000,000 rows in the Profit_Fact table. The Profit_Fact calcula-
tions are:

Average rows per block (buffer)
= floor((65,536 bytes/buffer)/(40 bytes/row)) = 1,638.
Number of buffers = ceiling(2,000,000 rows/(1,638 rows/buffer))
= 1,222.

Write cost (dedicated disk) = 1,222 × 2.2 ms ≈ 3 seconds.

Profit_Fact creation cost = query cost + write cost
≈ ≈

185 sec + 3 sec 188 seconds.