Databases Reference
In-Depth Information
Figure 2.3
Using a B+tree for multiple indexes.
Example: Mail-order Business
Assume we have a table of 10 million rows of mail-order customers for a large commer-
cial business. Customer rows have attributes for customer name, customer number,
street address, city, state, zip code, phone number, e-mail, employer, job title, credit rat-
ing, date of last purchase, and total amount of purchases. Assume that the average row
size is 250 bytes; block size is 5,000 bytes; pointer size, including row offset, is 5 bytes;
and composite key for jobTitle, city, and totalPur is 35 bytes. The table of 10 million
rows has 500,000 blocks since each block contains 20 rows. In general, rows tend to be
variable length, but for simplicity we assume they are equal length here.
The query to be analyzed is
SELECT empNo, empName, empAddress, empPhone, empEmail
FROM customer
WHERE jobTitle = 'software engineer'
