Databases Reference
In-Depth Information
Figure 5.3
Profit_by_invoice_date snapshot.
We have 200 days on record, so the Profit_by_Invioce_Date table has 200 rows.
The Profit_by_Invoice_Date calculations are:
Average rows per block (buffer)
= floor((65,536 bytes/buffer)/(32 bytes/row)) = 2,048.
Number of blocks (buffers) = ceiling(200 rows/(2,048 rows/block))
= 1.
×
Write cost (dedicated disk) = 1
2.2 ms = 2.2 ms.
Profit_by_Invoice_Date creation cost = query cost + write cost
= 185 sec + .0022 sec
≈
185 seconds.
The user query becomes as follows:
SELECT * FROM Profit_by_Invoice_Date;
The query I/O time calculations for this query in a shared disk follow:
×
Query I/O time (shared disk) = 1
5.8 ms = 5.8 ms.
Disk I/O time before Profit_by_Invoice_Date
= query frequency
×
I/O time per query
×
(487 sec/query)
= 2,435 seconds (just over 40 min)
Disk I/O time with Profit_by_Invoice_Date
= creation cost + query frequency
= (5 queries/day)
×
I/O time per query
= 185 sec creation cost + (5 queries/day)
×
≈
(5.8 ms/query)
185 seconds.
