- Pipeline for
a binary operator:
- Fact:
- The number of buffers required for input buffering can be very large
In fact:
- The availability of memory buffers will often dictate which implementation algorithm we can use to execute a specific binary (relational algebra) operation !!!
- Consider a
2 staged
join operation:
- We will consider the
buffer requirement for
pipelined execution
for the following
scenarios:
- ⋈1 = 1-pass algorithm and ⋈2 = 1-pass algorithm
- ⋈1 =
1-pass algorithm
and
⋈2 =
2-pass hash-based algorithm
(Due to time constraints, these cases are omitted)
- ⋈1 = 2-pass hash-based algorithm and ⋈2 = 1-pass algorithm
- ⋈1 = 2-pass hash-based algorithm and ⋈2 = 2-pass hash-based algorithm
From the discussion below, you should get a sense on when the buffers are:
- Allocated and
- De-allocated
- Buffer requirement for
the pipelined execution:
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)
(B(R) ≤ B(S) is guaranteed after we perform the optimization on the logical query plan)
- Buffers needed for
processing
⋈1:
Simultaneous buffer utilization = B(R) + 1
- While ⋈1 is
active, the
⋈1 operation will
produce
tuples that must be
indexed
by the
1-pass algorithm for
⋈2:
Simultaneous buffer utilization = B(R) + 1 + B(R⋈S)
- When ⋈1 operation
is completed (done),
we can
free the
buffers and
use
1 buffer to
scan the
relation T:
Simultaneous buffer utilization = 1 + B(R⋈S)
Conclusion:
- Buffer utilization (M) ≥ B(R) + 1 + B(R⋈S)
- Buffers needed for
processing
⋈1:
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)
- Alternative way to perform the
the pipelined execution:
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)
(B(R) ≤ B(S) is guaranteed after we perform the optimization on the logical query plan)
- Buffers needed for
processing
⋈1:
Simultaneous buffer utilization = B(R) + 1
- We index the
relation T:
Simultaneous buffer utilization = B(R) + 1 + B(T)
While ⋈1 is active, the ⋈1 operation will produce tuples that we immediately join with T:
Simultaneous buffer utilization = B(R) + 1 + B(T) + 0
Conclusion:
- Buffer utilization (M) ≥ B(R) + 1 + B(T)
- Buffers needed for
processing
⋈1:
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)
- Buffer requirement for
the pipelined execution:
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)
(B(R) ≤ B(S) is guaranteed after we perform the optimization on the logical query plan)
- Buffers needed for
processing
⋈1:
Simultaneous buffer utilization = B(R) + 1
- While ⋈1 is
active, the
⋈1 operation will
produce
tuples that must be
hashed
in pass 1 of the
2-pass hash-based algorithm for
⋈2:
Simultaneous buffer utilization = B(R) + 1 + k
- When ⋈1 operation
is completed (done),
we can
free
all
buffers used:
because the hashed backets are stored on disk
Simultaneous buffer utilization = 0
- We must use
k buckets to
hash
the relation T:
(The same # buckets must be used to ensure the same hash location for identical keys)
Simultaneous buffer utilization = k + 1
- After
we hash
relation T,
all buffers can be
free up:
- In pass 2 of the
2-pass hash-based join,
we use a 1-pass join algorithm
on each bucket:
Buffer requirement to for pass 2:
The sub-relations (= bucket) of R ⋈ S or T ≤ n <==> min( B(R ⋈ S), B(T) ) / k ≤ n ===> M ≥ min( B(R ⋈ S), B(T) ) / k + 1
Conclusion:
M ≥ B(R) + 1 + k ........... (from ⋈1) M ≥ min( B(R ⋈ S), B(T) ) / k + 1 ........... (from ⋈2)
- Buffers needed for
processing
⋈1:
The parameter k can be determined when M is given:
k = B(R) + 1 - M ........... (from ⋈1)In the next example, you will see how k is determined
- ⋈1 =
1-pass algorithm ---
assuming that
B(R) ≤ B(S)