Databases Reference
In-Depth Information
This query reads from the Invoice, Job_Costing, and Line_Item tables. We've
already calculated the number of blocks for the Job_Costing and the Line_Item tables.
We still need to calculate the number of blocks for the Invoice table. We average 5,000
invoices per day, with 200 days on record, giving 1,000,000 rows in the Invoice table.
The Invoice calculations are:
Average rows per block (buffer)
= floor((65,536 bytes/buffer)/(104 bytes/row)) = 630.
Number of blocks (buffers)
= ceiling(1,000,000 rows/(630 rows/buffer)) = 1,588.
There are two possible join orderings, leading to two alternative query plans.
Plan A:
Step 1: Merge-join Line_Item with Invoice (no sorting required)
Step 2: Merge-join step 1 result with Job_Costing (requires sorting step 1 result by
job_id)
Plan B:
Step 1: Merge-join Line_Item with Job_Costing (requires sorting Line_Item by
job_id)
Step 2: Merge-join step 1 result with Invoice (requires sorting step 1 result by
invoice_id)
Both plans require the three base tables to be scanned in full. Plan A is superior
since it requires much less sorting than plan B. After the merge-join of Line_Item with
Invoice, the only columns to be kept are invoice_date, job_id, and sell. The result is
2,000,000 rows of 20 bytes each for less than 10,000 blocks. The result is small enough
to sort in memory, so we only need to figure the time to scan the tables once.
Query I/O time (shared disk)
= scan Line_Item + scan Invoice + scan Job_Costing
= (3,054 blocks + 1,588 blocks + 79,366 buffers scanned)
×
5.8 ms
×
= 84,008
5.8 ms
= 487 seconds.
The same results could be obtained from the materialized view specified in Figure 5.3.
We can generate the materialized view from scratch during off hours by running
the same query, and then storing the results.
×
≈
Query I/O time (dedicated disk) = 84,008
2.2 ms
185 seconds.
