Query Optimization and Plan Selection - Physical Database Design - page 49

card('shipment') = 100 (Note: 'shipment' has 100 rows with shipdate
= '01-JUN-2006'.)

Now that we have the sizes of the two intermediate tables we can now apply Equa-
tion 3.6 to find the size of the final result of the join:

card('supplier' join 'shipment')
= S(snum)

× card('supplier') × card('shipment')

× ×

= (1/200) 4 100

= 2.

The final result is 2 rows, that is, all the suppliers in London with the ship date of 01-
JUN-2006.
We note that both ways of computing the final result have the same number of
rows in the result, but the number of block accesses for each is quite different. The cost
of doing the joins first is much higher than the cost for doing the selections first.

3.5.5 Example Estimations of Query Execution Plan Table Sizes

We now revisit Figures 3.1 and 3.2 for actual table sizes within the query execution plan
for Example Query 3.1.

Option 1A (Figure 3.1)

For the query execution plan in Figure 3.1 we first join supplier (S) and shipment (SH)
to form TEMPA. The size of TEMPA is computed from Equation 3.6 as

card(TEMPA) = S × (card(supplier) × card(shipment))

× ×

= 1/200 200 100,000 = 100,000 rows,

where S = 1/200, the selectivity of the common attribute in the join, snum.
Next we join TEMPA with the part table, forming TEMPB.

card(TEMPB) = S × card(TEMPA) × card(part)

× ×

= 1/100 100,000 100 = 100,000 rows,

where S = 1/100, the selectivity of the common attribute in the join, pnum.
Finally we select the 10% of the rows from the result that has city = 'NY', giving us
10,000 rows in TEMPC and the final result of the query. We note that the 10% ratio
holds through the joins as long as the joins involve primary key-foreign key pairs (and
do not involve the attribute city).