Databases Reference
In-Depth Information
Example Update 15.4
Add a new customer, a painter, with number 423378 and the customer's order number,
763521601, to the database.
INSERT INTO customer (custNum, jobTitle) VALUES
('423378','painter');
INSERT INTO order (orderNum, custNum) VALUES
('763521601','423378');
Analysis of Example Query 15.3
The system query optimizer can choose from a number of different ways to execute the
transaction, Query 15.3. Let us first assume that the tables are all ordered physically by
their primary keys. We use the sort-merge join strategy for the first transaction: Sort the
order table by custNum, then join tables order and customer with a single scan of each,
and select only rows that have jobTitle of computer engineer. We then project on order-
Num to answer the query. To simplify the analysis we assume that a sort of
nb
blocks
takes 2
nb
log
3
nb
block accesses (see Chapter 3) and that computer engineers make
up 5% of the customers and orders in the database.
All row accesses are sequential in this strategy. For simplicity we have a block size of
4 KB (4,096 bytes) and a prefetch buffer size of 64 KB, as done in DB2. We can esti-
mate the input/ouput (I/O) service time by first computing the effective prefetch block-
ing factors for the tables order, customer, orderCust, and compEngr: 4,369 (64 KB/15
bytes per row), 2,176, 1,680, and 1,680, respectively. We assume an IBM U320 146
GB hard drive with an average seek of 3.6 ms, an average rotational delay of 2 ms (for
15,000 RPM), and a transfer rate of 320 MB/sec.
×
I/O time for a block access in a table scan = rotational delay
+ transfer of a prefetch buffer
= 2 ms + 64 KB/320 MB/sec
= 2.2 ms.
Block accesses = sort order table + scan order table
+ scan customer table + create orderCust table
+ scan orderCust table + create compEngr table
+ project compEngr table
×
= (2
4,369 log
3
4,369) + 4,369 + 37 + 120 + 120 + 6 + 6
= 2
×
4,369
×
7.63 + 4,658
= 71,329.
×
I/O time = 71,329 block accesses
2.2 ms
= 156.9 seconds.
