Slideshow:
Problem description
(Click in figure to pull out the relation statistics)
Initailization step:
find the
best join ordering
for all join combinations of
1 relation
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of R and S: (R ⋈ S and S ⋈ R)
Updated table:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R | 1000 | 0 | R
S | 1000 | 0 | S
T | 1000 | 0 | T
U | 1000 | 0 | U
------------+-------------+---------+-----------------
R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked
an arbitrary ordering
------------+-------------+---------+-----------------
|
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of R and T: (R ⋈ T and T ⋈ R)
Updated table:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R | 1000 | 0 | R
S | 1000 | 0 | S
T | 1000 | 0 | T
U | 1000 | 0 | U
------------+-------------+---------+-----------------
R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked
R, T | 1000000 | 0 | R ⋈ T an arbitrary ordering)
------------+-------------+---------+-----------------
|
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of R and U: (R ⋈ U and U ⋈ R)
Updated table:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R | 1000 | 0 | R
S | 1000 | 0 | S
T | 1000 | 0 | T
U | 1000 | 0 | U
------------+-------------+---------+-----------------
R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked
R, T | 1000000 | 0 | R ⋈ T an arbitrary ordering)
R, U | 10000 | 0 | R ⋈ U
------------+-------------+---------+-----------------
|
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of S and T: (S ⋈ T and T ⋈ S)
Updated table:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R | 1000 | 0 | R
S | 1000 | 0 | S
T | 1000 | 0 | T
U | 1000 | 0 | U
------------+-------------+---------+-----------------
R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked
R, T | 1000000 | 0 | R ⋈ T an arbitrary ordering)
R, U | 10000 | 0 | R ⋈ U
S, T | 2000 | 0 | S ⋈ T
------------+-------------+---------+-----------------
|
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of S and U: (S ⋈ U and U ⋈ S)
Updated table:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R | 1000 | 0 | R
S | 1000 | 0 | S
T | 1000 | 0 | T
U | 1000 | 0 | U
------------+-------------+---------+-----------------
R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked
R, T | 1000000 | 0 | R ⋈ T an arbitrary ordering)
R, U | 10000 | 0 | R ⋈ U
S, T | 2000 | 0 | S ⋈ T
S, U | 1000000 | 0 | S ⋈ U
------------+-------------+---------+-----------------
|
Iteration step 1:
find the
best join ordering
for all join combinations of
2 relations
Considering all combination of T and U: (T ⋈ U and U ⋈ T)
Updated table: (click to pull out before continueing)
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Now we compute the optimal solution for all combinations of size 3:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R,S,T | ??? | ??? | ???
R,S,U | ??? | ??? | ???
R,T,U | ??? | ??? | ???
S,T,U | ??? | ??? | ???
------------+-------------+---------+-----------------
|
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, T:
Which 3 way join ordering has ther least cost (= # tuples in the intermediate result(s)) ???
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, T:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, T:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, T:
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
We have found our first optimal solution of size 3:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R
R,S,U | ??? | ??? | ???
R,T,U | ??? | ??? | ???
S,T,U | ??? | ??? | ???
------------+-------------+---------+-----------------
|
Next: combination of R, S, U
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, U:
Which 3 way join ordering has ther least cost (= # tuples in the intermediate result(s)) ???
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, S, U:
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
We have found our second optimal solution of size 3:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R
R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U
R,T,U | ??? | ??? | ???
S,T,U | ??? | ??? | ???
------------+-------------+---------+-----------------
|
Next: combination of R, T, U
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, T, U:
Which 3 way join ordering has ther least cost (= # tuples in the intermediate result(s)) ???
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, T, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, T, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for R, T, U:
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
We have found our third optimal solution of size 3:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R
R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U
R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R
S,T,U | ??? | ??? | ???
------------+-------------+---------+-----------------
|
Next: combination of S, T, U
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for S, T, U:
Which 3 way join ordering has ther least cost (= # tuples in the intermediate result(s)) ???
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for S, T, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for S, T, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Finding the best join ordering for S, T, U:
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
Iteration step 2:
find the
best join ordering
for all join combinations of
3 relations
We have found all optimal solution of size 3:
Relations | size of | Cost of | Best join
joined | join result | join | ordering (plan)
------------+-------------+---------+-----------------
R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R
R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U
R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R
S,T,U | 2000 | 1000 | (T ⋈ U) ⋈ S
------------+-------------+---------+-----------------
|
Updated table: (click to pull out before continueing)
Iteration step 3:
find the
best join ordering
for all join combinations of
4 relations
Now we compute the optimal solution for all combinations of size 4:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T,U | ??? | ??? | ??? ------------+-------------+---------+----------------- |
This row will give us the optimal solution !!!
Iteration step 3:
find the
best join ordering
for all join combinations of
4 relations
Finding the best join ordering for R, S, T, U:
Which 4 way join ordering has ther least cost (= # tuples in the intermediate result(s)) ???
Iteration step 3:
find the
best join ordering
for all join combinations of
4 relations
Finding the best join ordering for R, S, T, U:
Best solution order and its cost for 3 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
4 relations
Finding the best join ordering for R, S, T, U:
Best solution order and its cost for 2 relations join are given in the pull out !!!
Iteration step 2:
find the
best join ordering
for all join combinations of
4 relations
Finding the best join ordering for R, S, T, U:
With cost = 3000
DONE !!!
- Problem description:
- Find the least cost
join ordering for:
R(a,b) ⋈ S(b,c) ⋈ T(c,d) ⋈ U(d,a)that is a left-deep join tree
- Find the least cost
join ordering for:
- 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
- Initialization for
subsets of relations
of size = 1:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R | 1000 | 0 | R S | 1000 | 0 | S T | 1000 | 0 | T U | 1000 | 0 | U ------------+-------------+---------+-----------------
- Initialization for
subsets of relations
of size = 2:
- R, S:
T(R) = 1000 T(S) = 1000 // T(R) == T(S) !!! Best join ordering: R ⋈ S // Just pick an order arbitrarily T(R) × T(S) T(R ⋈ S) = -------------------- max( V(R,b),V(S,b) ) 1000 × 1000 = ------------- = 5000 max(200,100)
R ⋈ S has no intermediate result: ⋈ / \ ===> Cost of (R ⋈ S) = 0 R SUpdated table:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R | 1000 | 0 | R S | 1000 | 0 | S T | 1000 | 0 | T U | 1000 | 0 | U ------------+-------------+---------+----------------- R, S | 5000 | 0 | R ⋈ S (Since, T(R) = T(S), we can pick any order)
- R, T:
T(R) = 1000 T(T) = 1000 // T(R) == T(T) !!! Best join ordering: R ⋈ T // Just pick an order arbitrarily T(R ⋈ T) = T(R) × T(T) // No common attr ==> Cartesian product !! = 1000 × 1000 = 1,000,000
R ⋈ T has no intermediate result: ⋈ / \ ===> Cost of (R ⋈ T) = 0 R TUpdated table:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R | 1000 | 0 | R S | 1000 | 0 | S T | 1000 | 0 | T U | 1000 | 0 | U ------------+-------------+---------+----------------- R, S | 5000 | 0 | R ⋈ S R, T | 1000000 | 0 | R ⋈ T
- R, U:
T(R) = 1000 T(U) = 1000 // T(R) == T(U) !!! Best join ordering: R ⋈ U // Just pick an order arbitrarily T(R) × T(U) T(R ⋈ U) = -------------------- max( V(R,a),V(U,a) ) 1000 × 1000 = ------------- = 10,000 max(100,50)
- S, T:
T(S) = 1000 T(T) = 1000 // T(S) == T(T) !!! Best join ordering: S ⋈ T // Just pick an order arbitrarily T(S) × T(T) T(S ⋈ T) = -------------------- max( V(S,c),V(T,c) ) 1000 × 1000 = ------------- = 2,000 max(500,20)
- S, U:
T(S) = 1000 T(U) = 1000 // T(S) == T(U) !!! Best join ordering: S ⋈ U // Just pick an order arbitrarily T(S ⋈ U) = T(S) × T(U) // No common attr ==> Cartesian product = 1000 × 1000 = 1,000,000
- T, U:
T(T) = 1000 T(U) = 1000 // T(T) == T(U) !!! Best join ordering: T ⋈ U // Just pick an order arbitrarily T(T) × T(U) T(R ⋈ U) = -------------------- max( V(T,d),V(U,d) ) 1000 × 1000 = ------------- = 1,000 max(50,1000)
Result:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R | 1000 | 0 | R S | 1000 | 0 | S T | 1000 | 0 | T U | 1000 | 0 | U ------------+-------------+---------+----------------- R, S | 5000 | 0 | R ⋈ S (T(R) = T(S), we picked R, T | 1000000 | 0 | R ⋈ T an arbitrary ordering) R, U | 10000 | 0 | R ⋈ U S, T | 2000 | 0 | S ⋈ T S, U | 1000000 | 0 | S ⋈ U T, U | 1000 | 0 | T ⋈ U ------------+-------------+---------+----------------- - R, S:
- Now we compute the
optimal solution for
all subset of
size 3:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | ??? | ??? | ??? R,S,U | ??? | ??? | ??? R,T,U | ??? | ??? | ??? S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------
- Best ordering for
R,S,T:
- All possible
left-deep
join order
using R,S,T:
- Use the best solution for
2 relations to find
candidate solution for
3 relations:
The corresponding candidate (left-deep) join trees are:
- Compute the
cost of
each join tree:
(Recall: cost of join tree = total size of the intermediate result relations - not including the root node)
- Therefore,
the best join ordering for
{R,S,T} is:
( S ⋈ T ) ⋈ R
and this ordering incurs a cost of 2000 (tuples)
- We still need to
compute the
size of
T(
( S ⋈ T ) ⋈ R
)
for the table entry:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | ??? | 2000 | ( S ⋈ T ) ⋈ R R,S,U | ??? | ??? | ??? R,T,U | ??? | ??? | ??? S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------Computation:
T (S ⋈ T) × T( R ) T( (S ⋈ T) ⋈ R ) = --------------------------- max( V(S⋈T, b), V(R, b) ) 2000 × 1000 = -------------- max(200, 100) 2000 × 1000 = -------------- = 10,000 200
- Therefore, the
completed row is:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | ??? | ??? | ??? R,T,U | ??? | ??? | ??? S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------
- All possible
left-deep
join order
using R,S,T:
- Best ordering for
R,S,U:
- All possible
left-deep
join order
using R,S,U:
- Use the best solution for
2 relations to find
candidate solution for
3 relations:
The corresponding candidate (left-deep) join trees are:
- Compute the
cost of
each join tree:
- Therefore,
the best join ordering for
{R,S,U} is:
( R ⋈ S ) ⋈ U
and this ordering incurs a cost of 5000 (tuples)
- We still need to
compute the
size of
T(
( R ⋈ S ) ⋈ U
)
for the table entry:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | ??? | 5000 | (R ⋈ S) ⋈ U R,T,U | ??? | ??? | ??? S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------Computation:
T (R ⋈ S) × T( U ) T( (R ⋈ S) ⋈ U ) = --------------------------- max( V(R⋈S, a), V(U, a) ) 5000 × 1000 = -------------- max(100, 50) 5000 × 1000 = -------------- = 50,000 100
- Therefore:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | ??? | ??? | ??? S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------
- All possible
left-deep
join order
using R,S,U:
- Best ordering for
R,T,U:
- All possible
left-deep
join order
using R,T,U:
- Use the best solution for
2 relations to find
candidate solution for
3 relations:
The corresponding candidate (left-deep) join trees are:
- Compute the
cost of
each join tree:
- Therefore,
the best join ordering for
{R,T,U} is:
( T ⋈ U ) ⋈ R
and this ordering incurs a cost of 1000 (tuples)
- We still need to
compute the
size of
T(
( T ⋈ U ) ⋈ R
)
for the table entry:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | ??? | 1000 | (T ⋈ U) ⋈ R S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------Computation:
T (T ⋈ U) × T( R ) T( (T ⋈ U) ⋈ R ) = --------------------------- max( V(T⋈U, a), V(R, a) ) 1000 × 1000 = -------------- max(100, 50) 1000 × 1000 = -------------- = 10,000 100
- Therefore:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------
- All possible
left-deep
join order
using R,T,U:
- Best ordering for
S,T,U:
- All possible
left-deep
join order
using S,T,U:
- Use the best solution for
2 relations to find
candidate solution for
3 relations:
The corresponding candidate (left-deep) join trees are:
- Compute the
cost of
each join tree:
- Therefore,
the best join ordering for
{S,T,U} is:
( T ⋈ U ) ⋈ S
and this ordering incurs a cost of 1000 (tuples)
- We still need to
compute the
size of
T(
( T ⋈ U ) ⋈ S
)
for the table entry:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R S,T,U | ??? | 1000 | (T ⋈ U) ⋈ S ------------+-------------+---------+-----------------Computation:
T (T ⋈ U) × T( S ) T( (T ⋈ U) ⋈ S ) = --------------------------- max( V(T⋈U, c), V(S, c) ) 1000 × 1000 = -------------- max(500, 20) 1000 × 1000 = -------------- = 2,000 500
- Therefore:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R S,T,U | 2000 | 1000 | (T ⋈ U) ⋈ S ------------+-------------+---------+-----------------
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R | 1000 | 0 | R S | 1000 | 0 | S T | 1000 | 0 | T U | 1000 | 0 | U ------------+-------------+---------+----------------- R, S | 5000 | 0 | R ⋈ S R, T | 1000000 | 0 | R ⋈ T R, U | 10000 | 0 | R ⋈ U S, T | 2000 | 0 | S ⋈ T S, U | 1000000 | 0 | S ⋈ U T, U | 1000 | 0 | T ⋈ U ------------+-------------+---------+----------------- R,S,T | 10000 | 2000 | (S ⋈ T) ⋈ R R,S,U | 50000 | 5000 | (R ⋈ S) ⋈ U R,T,U | 10000 | 1000 | (T ⋈ U) ⋈ R S,T,U | 2000 | 1000 | (T ⋈ U) ⋈ S ------------+-------------+---------+----------------- R,S,T,U | ??? | ??? | ??? - All possible
left-deep
join order
using S,T,U:
- Now we compute the
optimal solution for
all subset of
size 4:
Relations | size of | Cost of | Best join joined | join result | join | ordering (plan) ------------+-------------+---------+----------------- R,S,T,U | ??? | ??? | ??? ------------+-------------+---------+-----------------
- Best ordering for
R,S,T,U:
- All possible
left-deep
join order
using R,S,T,U:
- Use the best solution for
3 relations to find
candidate solution for
4 relations:
The corresponding candidate (left-deep) join trees are:
- To compare the
different trees,
we compute the
cost of
each join tree:
(Recall: cost of join tree = total size of the intermediate result relations - not including the root node)
- The best
left-deep join tree is:
- All possible
left-deep
join order
using R,S,T,U:
- Cost estimate:
- We used a simple
cost estimate function:
Cost ( join tree ) = # tuples in intermediate results T(R) × T(S) = ---------------------- max( V(R,A), V(S,A) )
- If you have histogram information, you can use this information to compute a better estimate on the output size of the intermediate results
- We used a simple
cost estimate function: