Slideshow:
Review of the Nested-loop Algorithm
Recall the nested-loop join algorithm:
Review of the Nested-loop Algorithm
Review of the nested-loop join algorithm:
Review of the Nested-loop Algorithm
Review of the nested-loop join algorithm:
Property of the Nested-loop Algorithm
Property of the nested-loop join algorithm:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Consider the nested loop execution using:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Notice that: the 2nd input "relation" is computed !!!
I.e.: we must execute the operation again if we want to obtain the tuples again !!
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Notice that: the 2nd input "relation" is computed !!!
The execution will read relation T once for the first chunk of U tuples
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Notice the anomaly in the behavior of the nested-loop algorithm:
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Behavior of nested join algorithm for the join operator ⋈3:
Notice the anomaly in the behavior of the nested-loop algorithm:
(because ⋈2 has been executed multiple times by the query plan!!!)
Nested-loop algorithm using non-left-deep trees
may read 1st relation
multiple times
Summary:
Why left-deep trees work well for the nested-loop join algorithm
Consider the nested loop execution using:
Why left-deep trees work well for the nested-loop join algorithm
Behavior of nested join algorithm for the join operator ⋈1:
Why left-deep trees work well for the nested-loop join algorithm
Behavior of nested join algorithm for the join operator ⋈1:
The difference is: the right sub-trees only contain relations (i.e.: no operators !!)
Why left-deep trees work well for the nested-loop join algorithm
Behavior of nested join algorithm for the join operator ⋈1:
Why left-deep trees work well for the nested-loop join algorithm
Behavior of nested join algorithm for the join operator ⋈2:
The difference again is: the right sub-trees only contain relations (i.e.: no operators !!)
Why left-deep trees work well for the nested-loop join algorithm
- Recall the nested-loop
join algorithm:
while ( S ≠ empty ) { Read M - 1 blocks of S and organize them into a search structure (e.g., hash table)' Rewind R; // Read R multiple times while ( R ≠ empty ) { Read 1 block (b) of R; for ( each tuple t ∈ block b ) do { Find the tuples s1, s2, ... of S (in the search structure) that join with t Output (t,s1), (t,s2), ... } } }
- Summary of
the execution behavior of the
nested loop algorithm:
- Read the
first M-1 blocks of
R and
scan S once to
join:
- Read the
next M-1 blocks of
R and
scan S again to
join:
And so on...
- Property:
- The 2nd relation is
accessed
multiple times
- However: the 1st relation is accessed only once !!!
- The 2nd relation is
accessed
multiple times
- Read the
first M-1 blocks of
R and
scan S once to
join:
- Condider the
processing of
R ⋈ S ⋈ T ⋈ U
using a non-left deep
join tree
(e.g., a
right-deep
join tree)
- Query plan using a
right-deep join tree:
- Consider the
behavior of
nested join algorithm for
the join operator ⋈3:
(Ignore the buffer allocation --- focus of the behavior of the algorithm)
- The join operation
⋈3 will
read
a fragment of its
first (=left) operand:
- Then
the join operator
⋈3 will
access ("read")
all tuples from
the second (= right) operand:
However, in this case:
- The tuples from the
2nd operand must be
computed
with a query execution:
- The tuples from the
2nd operand must be
computed
with a query execution:
- After processing
the first fragment
of U,
the join operation
⋈3 will
read
another
fragment of
U:
- Then
the join operator
⋈3 will
"read" (= compute
again !!)
all tuples from
its second (= right) operand:
We will execute this query again to "find" (= compute !!) the tuples:
And so on !!!!
Result:
- The nested loop operator
⋈2
will access their
first relation
multiple times !!!!
- This is a result from the fact that the sub-query is executed multiple times !!!
- The nested loop operator
⋈2
will access their
first relation
multiple times !!!!
- The join operation
⋈3 will
read
a fragment of its
first (=left) operand:
- Query plan using a
right-deep join tree:
- Conclussion:
- Any join tree where the
2nd operand is
not a
relation:
will cause the nested loop to execute a sub-query multiple times !!!
- A query plan should execute each operation exactly once to be optimal !!!
- Therefore:
- The nested-loop join algorithm behaves unexpectedly differently when using a non-left-deep join tree
- Any join tree where the
2nd operand is
not a
relation:
- Processing
R ⋈ S ⋈ T ⋈ U
using a left-deep join tree:
- Query plan using a
left-deep
join tree:
- The result is
again
passed through
pipelining:
- The ⋈1 will
read the
fragments of
relation R:
For each fragment, ⋈1 will read all tuples in the relation S:
Notice:
- Tuples from
S can be
read directly from
the relation (file) S
We do not need to run a query to obtain tuples from relation S !!!
- Tuples from
S can be
read directly from
the relation (file) S
- Eventually,
the ⋈2 will
start
executing
(got enough tuples
in buffer):
For each buffer-full of tuples, ⋈2 will read all tuples in the relation T:
Notice:
- Tuples from
T can be
read directly from
the relation (file) T
We do not need to run a query to obtain tuples from relation T !!!
- Tuples from
T can be
read directly from
the relation (file) T
- And so on...
Conclusion:
- The nested-loop algorithm
behaves
"normally":
- Because every "2nd" relation
in the left-deep tree is
an actual relation
(and not
a query sub-tree):
- Multiple scans on the 2nd relation does not result in multiple executions of a query sub-tree
- Because every "2nd" relation
in the left-deep tree is
an actual relation
(and not
a query sub-tree):
- The nested-loop algorithm
behaves
"normally":
- Query plan using a
left-deep
join tree: