Databases Reference
In-Depth Information
We have 5,000 customers, so the Profit_by_Customer table has 5,000 rows. The
Profit_by_Customer calculations are:
Average rows per block (buffer)
= floor((65,536 bytes/block)/(28 bytes/row)) = 2,340.
Number of blocks (buffers)
= ceiling(5,000 rows/(2,340 rows/block)) = 3.
×
Write cost (dedicated disk) = 3
2.2 ms = 6.6 ms.
Profit_by_Customer creation cost = query cost + write cost
≈
185 sec + .0066 sec
≈
185 seconds.
The user query becomes as follows:
SELECT * FROM Profit_by_Customer;
The query I/O time calculations for a shared disk follow.
Query I/O time (sequential scan of entire table, shared disk)
= 3
×
5.8 ms = 17.4 ms.
Disk I/O time before Profit_by_Customer
= query frequency
×
I/O time per query
×
(487 sec/query) = 1,461 seconds.
Disk I/O time with Profit_by_Customer
= creation cost + query frequency
= (3 queries/day)
I/O time per query
= 185 sec creation cost + (3 queries/day)
×
×
≈
(0.017 sec/query)
185 seconds.
Figure 5.5
Profit fact table.
