1. Another way to reduce the size of temporary results is to choose an optimal ordering of the join operations.

2. Natural join is associative:

(r1 ∝ r2) ∝ r3 = r1 ∝ (r2 ∝ r3)

3. Although these expressions are equivalent, the costs of computing them may differ.

• Look again at our expression

â…¡bname;assets ((∑ccity="Port Chester"(customer)) ∝ deposit ∝ branch)

• we see that we can compute deposit 1 branch first and then join with the first part.
• However, deposit 1 branch is likely to be a large relation as it contains one tuple for every account.
• The other part,

∑ccity="Port Chester"(customer)

is probably a small relation (comparatively).

• So, if we compute

∑ccity="Port Chester"(customer) ∝ deposit first, we get a reasonably small relation.

• It has one tuple for each account held by a resident of Port Chester.
• This temporary relation is much smaller than deposit 1 branch.

4. Natural join is commutative:

r1 ∝ r2 = r2 ∝ r1

• Thus we could rewrite our relational algebra expression as:

â…¡bname,assets (((∑ccity="Port Chester"(customer)) ∝ branch) ∝ deposit)

• But there are no common attributes between customer and branch, so this is a Cartesian product.
• Lots of tuples!
• If a user entered this expression, we would want to use the associativity and commutativity of natural join to transform this into the more effcient expression we have derived earlier (join with deposit rst, then with branch).