Databases Reference
In-Depth Information
Analysis of Update 15.4
The strategy to execute the second transaction, Update 15.4, using the same schema, is
to scan each table (order and customer) and rewrite both tables in the new order.
Block accesses = scan order table + scan customer table
+ rewrite order table + rewrite customer table
= 4,369 + 37 + 4,369 + 37
= 8,812.
×
I/O time = 8,812 block accesses
2.2 ms
= 19.4 seconds.
Defining the Denormalized Table orderCust
If we combine the customer and order tables to avoid the join in Query 15.3, the result-
ing schema will have a single table orderCust, with primary key orderNum and nonkey
attributes custNum and jobTitle, instead of separate tables order and customer. This not
only avoids the join, but also the sort needed to get both tables ordered by custNum.
CREATE TABLE orderCust (orderNum CHAR(9),
custNum CHAR(6) not null,
jobTitle VARCHAR(256),
PRIMARY KEY (orderNum);
The strategy for Query 15.3 is now to scan orderCust once to find the computer
engineers, write the resulting data on disk, and then read back from disk to project the
resulting temporary table, compEngr, to answer the query.
Block accesses = scan orderCust + write 5% of orderCust on disk
+ project 5% of orderCust
= 120 + 6 + 6
= 132.
I/O time = 132 block accesses
×
2.2 ms
= .3 second.
The strategy for Update 15.4, using this refined schema, is to scan orderCust once
to find the point of insertion and then to scan again to reorder the table.
Block accesses = scan orderCust + scan orderCust
= 120 + 120
= 240.
