- Suppose we want to compare
the cost between
the following 2
join trees:
- Since the # disk IOs
is proportional to the
size of the
data set, we can:
- Use
the size of
each operand as
an approximation
- This fact is especially true
for the most efficient
one-pass join algorithm which:
- Access each operand exactly once
The following diagram shows the size of each operand of the join operators:
- Use
the size of
each operand as
an approximation
- The join tree 1
using the one-pass join algorithm
will incur the following
number of
disk IOs:
- The inner join
R ⋈ S will access
B(R) + B(S) data blocks = ~ ( T(R) + T(S) ) / k (i.e.: proportional to T(R) + T(S) )
- The outer join
"(R⋈S)" ⋈ S
will access
~ ( T(R⋈S) + T(T) ) / k data blocks (i.e.: proportional to T(R⋈S) + T(T) )
- Total # block accessed:
~ ( T(R) + T(S) + T(R⋈S) + T(T) ) / k blocks
- The inner join
R ⋈ S will access
- The join tree 2
using the one-pass join algorithm
will incur the following
number of
disk IOs:
- The inner join
T ⋈ R will access
B(T) + B(R) data blocks = ~ ( T(T) + T(R) ) / k (i.e.: proportional to T(T) + T(R) )
- The outer join
"(T⋈R)" ⋈ S
will access
~ ( T(T⋈R) + T(S) ) / k data blocks (i.e.: proportional to T(T⋈R) + T(S) )
- Total # block accessed:
~ ( T(T) + T(R) + T(T⋈R) + T(S) ) / k blocks
- The inner join
T ⋈ R will access
- The difference in
cost between the
2 join trees is:
Join tree 1: cost ~= ( T(R) + T(S) + T(T) + T( R ⋈ S ) ) / k Join tree 2: cost ~= ( T(R) + T(S) + T(T) + T( T ⋈ R ) ) / k ----------------------------------------------------------------- Difference in cost ~= T( R ⋈ S ) - T( T ⋈ R ) / k
- Conclusion:
- The difference in
cost between
different join trees depends
- only on the size of the intermediate results !!!
- The difference in
cost between
different join trees depends
- The cost of a
join tree is defined as
follows
Cost of join tree = ∑ (size of all internal nodes)
Note: 1. Do not include the root node Because: all join trees will produce the same result set 2. Do not include the leaf nodes Becasue the cost of the leaf nodes will cancel each other out