Slideshow:
Important fact (and difference) about the
join (⋈) operator
Important fact about the join (⋈) operator:
Important fact (and difference) about the
join (⋈) operator
- Example
How to compute the join efficiently
We can compute R ⋈ S by: storing all joining tuples of R in memory and scan S (using 1 buffer):
And then:
And so on...
Important fact (and difference) about the
join (⋈) operator
- Worst case scenario
Important fact (and difference) about the
join (⋈) operator
- Worst case scenario
(To alleviate the worst case scenario, we can use the nested-loop algorithm which will scan S multiple times)
Join algorithms based on TPMMS
- Important Fact:
- In order to
perform a
join operation
(without accessing
a relation more than once):
- All the joining tuples from both relations must reside in main memory
because the compare operation can only be performed on data stored in memory
Example:
R = { (a, 1), (a, 2), (a, 3), (b, 5), (b, 7), ... } S = { (a, 8), (a, 9), (b, 1), (b, 4), ... } To compute R ⋈ S, these sets of tuples must reside in memory: a: (a, 1), (a, 2), (a, 3) (a, 8), (a, 9) b: (b, 5), (b, 7) (b, 1), (b, 4) ..: ...
- In order to
perform a
join operation
(without accessing
a relation more than once):
- Example:
- If we want to
compute
the join on
the
relations
R ⋈ S
(without accessing
a relation more than once):
then all joining tuples (e.g.: all tuples that join on the value y2) must reside in memory (but not all of them need to be in memory simultaneously):
- If we want to
compute
the join on
the
relations
R ⋈ S
(without accessing
a relation more than once):
- Important observation:
- Only the
joining tuples from
one relation (R or S) need to
be (completely stored in memory
- We can scan the joining tuples from the other relation (S or R) and perform the join
- Only the
joining tuples from
one relation (R or S) need to
be (completely stored in memory
- Example:
- We can compute
R ⋈ S by:
storing all joining tuples of R
in memory
and scan S
(using 1 buffer)):
And then:
- We can compute
R ⋈ S by:
storing all joining tuples of R
in memory
and scan S
(using 1 buffer)):
- Suppose
every tuple in
R and
S
join with
each other:
Then:
- We will need to
bring all tuples in
R
(or
S) into
the main memory
to join !!!
- The result of the join is equal to the cartesian product !!!
- We will need to
bring all tuples in
R
(or
S) into
the main memory
to join !!!
-
In the worst case scenario,
the
memory requirement would be:
- M ≥
B(R) + 1
(store R in
memory and
use 1 buffer to scan the tuples in relation S)
even if R and S are sorted (sorting does not help (already sorted)) !!!!
Comment:
- This is the same algorithm
as the one-pass join algorithm
where we store the
entire relation R in
memory
(and use 1 buffer to scan the tuples of relation S)
- M ≥
B(R) + 1
(store R in
memory and
- The Nested-loop join approach:
- We bring a
portion of
one (smaller) relation into
the memory
(using M−1 buffers)
- We use 1 buffer to
scan the
other (larger) relation and
perform the join operation
- We must access the inner relation multiple times !!
See: click here
- We bring a
portion of
one (smaller) relation into
the memory
(using M−1 buffers)
- We will study 2 versions
of the 2-pass Join Algorithm
that is based on
TPMMS:
- Version 1:
(IO-efficient join)
- Requires
3 B(R) + 3 B(S)
disk I/O operations
- But: will only works when the number of joining tuples in R and S is small (contained in O(1) #buffer)
- Requires
3 B(R) + 3 B(S)
disk I/O operations
- Version 2:
(IO-intensive join)
- Can make all M buffers
available to hold
joining tuples in
R (or S)
- But: requires 5 B(R) + 5 B(S) disk I/O operations
- Can make all M buffers
available to hold
joining tuples in
R (or S)
- Version 1:
(IO-efficient join)