---

A simple greedy heuristic to find good join ordering

---

---

---

 

Slideshow:

The optimum join order - revisited
The best join ordering problem:
 
Although there is an exact algorithm (based on dynamic programming) available, the algorithm running time can be quick high (not polynomial))
In this webpage, we study a linear time heuristic to find the best join ordering
A heuristic for finding the best join ordering of N relations:
A heuristic for finding the best join ordering of N relations:

Example of applying the best join order heuristic
 
(Click in figure to pull out the relation statistics)
Example of applying the best join order heuristic - Iteration 1

Example of applying the best join order heuristic - Iteration 1

R, S:

R, T:

Example of applying the best join order heuristic - Iteration 1

R, U:

S, T:

Example of applying the best join order heuristic - Iteration 1

S, U:

T, U:

Example of applying the best join order heuristic - Iteration 1

Therefore:

Example of applying the best join order heuristic - Iteration 1

Therefore:

Let's pick:   T ⋈ U...
Example of applying the best join order heuristic - Iteration 2

S₁ = T ⋈ U   (from last result !)
Example of applying the best join order heuristic - Iteration 2

T ⋈ U and R:

Example of applying the best join order heuristic - Iteration 2

T ⋈ U and S:

Example of applying the best join order heuristic - Iteration 2

Therefore:

Example of applying the best join order heuristic - Iteration 3

S₂ = (T ⋈ U) ⋈ S   (from last result !)
Example of applying the best join order heuristic - Iteration 3

(T ⋈ U) ⋈ S and R:

Final remark
 
❮ ❯



---

---

---

---

• A greedy heuristic
  • Problem Description:

    Given a join on N relations R1, R2, ... RN: R1 ⋈ R2 ⋈ ... ⋈ RN Find the join ordering that has the least cost (Because ⋈ is communitative and associatitative, we can re-order the operands and still obtain the same result --- like +)

    ---

    ---

  • The greedy heuristic:

    1. Find Rk1 ⋈ Rk2 such that: T(Rk1 ⋈ Rk2) has least # tuples Let: S1 = Rk1 ⋈ Rk2 where T(Rk1) ≤ T(Rk2) 2. Find Rk3 in the remaining relations such that: T(S1 ⋈ Rk3) has least # tuples Let: S2 = S1 ⋈ Rk3 3. Find Rk4 in the remaining relations such that: T(S2 ⋈ Rk4) has least # tuples Let: S3 = S2 ⋈ Rk4 And so on... Solution is: ( ( Rk1 ⋈ Rk2 ) ⋈ Rk3 ) ⋈ Rk4 ....

  ---

  ---

  ---

  ---

• A worked out example
  • Statistics on the input relations:

    R(a,b) S(b,c) T(c,d) U(d,a) ================================================================= a V(R,a) = 100 V(U,a) = 50 b V(R,b) = 200 V(S,b) = 100 c V(S,c) = 500 V(T,c) = 20 d V(T,d) = 50 V(U,d) = 1000 ================================================================= T(R) = 1000 T(S) = 1000 T(T) = 1000 T(U) = 1000

    ---

    ---

  • Iteration 1:

    Find the smallest result set among joins between R, S, T and U: Join Size of result (See: click here) -------------------+-------------------------------------- R ⋈ S (or S ⋈ R) | 5000 R and T | 1000000 R and U | 10000 S and T | 2000 S and U | 1000000 T and U | 1000 <--- minimum cost So the best solution is using T and U: T ⋈ U or U ⋈ T Since: T(T) == T(U) // 1000 == 1000 we pick the join ordering between T and U arbitrarily... Best ordering: T ⋈ U

    ---

    ---

  • Iteration 2:

    Find the smallest result set among joins between T ⋈ U and (the remaining relations) R and S: Join Size of result (See: click here) -------------------+-------------------------------------- (T ⋈ U) ⋈ R | 10000 (T ⋈ U) ⋈ S | 2000 Best ordering: (T ⋈ U) ⋈ S

    ---

    ---

  • Iteration 3:

    Find the smallest result set among joins between (T ⋈ U) ⋈ S and R There is no choice but to pick R !!! Best ordering: ( (T ⋈ U) ⋈ S ) ⋈ R

    ---

    ---

  • Final Answer:

    ( (T ⋈ U) ⋈ S ) ⋈ R

    ---

    ---

  • Note:

    This happens to be the same answer as obtained by the dynamic programming algorithm The dynamic programming approach is guaranteed to find the least cost left-deep join ordering The greedy heuristic provides no guarantee (but it's much faster !!!)

    ---

  • Recommendation:

    Use greedy heuristic when number of relations is large Because: The Dynamic Programming algorithm will take too long to find the best join ordering !!!! We rather use a sub-optimal join ordering !!!