Slideshow:
Finding the best implementation algorithm for an (optimal)
query plan
Problem description:
What is the best implementation algorithm that can execute a given query plan with the given M buffers ?
Finding the best implementation algorithm for an (optimal) query plan
We solve this problem by a search in the (small) set of implementation algorithms:
Preliminary discussion
Preliminary discussion
I.e.: instead of giving you value for V(R.x) and V(S,x) and then compute the # tuples in B(R ⋈ S), we will simply give you the # tuples in B(R ⋈ S) (= k).
- In the query optimization problem,
we have
found/given
the following
information:
- We have determined
a (left deep) logical query plan
- We are given
a certain number
of memory buffers (M)
Example:
We must now find:
- The best algorithms to
execute
the
operation
⋈1 and
⋈2
(using pipelining)
Example:
- We have determined
a (left deep) logical query plan
- The best algorithms for
⋈1 and
⋈2 are
found by
a brute force search:
best ⋈1 = null; best ⋈2 = null; best_cost = ∞; for ( ⋈1 = {one-pass, 2-pass, 3-pass, ...} ) do { for ( ⋈2 = {one-pass, 2-pass, 3-pass, ...} ) do { if ( memory-requirement(⋈1, ⋈2) ≤ M ) { if ( cost(⋈1, ⋈2)) < best_cost ) { best ⋈1 = ⋈1; best ⋈2 = ⋈2; } } } }We will illustrate the search algorithm with a few examples next....
- Fact:
- The answer on
whether we have
sufficient number of
memory buffers
to execute
the query plan will
depends on:
The size of B(R ⋈ S)
- The size of
R ⋈ S is
estimate with:
B(R) × B(S) B(R ⋈ S) = ----------------------- max( V(R,x), V(S,x) )
- The answer on
whether we have
sufficient number of
memory buffers
to execute
the query plan will
depends on:
- For our examples, we
will assume that:
B(R) × B(S) B(R ⋈ S) = ----------------------- = k blocks max( V(R,x), V(S,x) )We will vary the the value k and study the effect.
- What will be
discussed
in the examples in the
next few
webpages:
- For a given value of
output size
(= k blocks) of the
join result
R ⋈ S, we
determine:
- The best suitable join algorithm for ⋈1 and ⋈2
I.e.: Which algorithms will use ≤ M buffers and have the least number of disk IOs
- For a given value of
output size
(= k blocks) of the
join result
R ⋈ S, we
determine: