Slideshow:
The bag intersection
(∩B) operator
Note: we need a search structure that contains (key, count (# occurences)) !
The bag intersection
(∩B) operator -
observation
The bag intersection
(∩B) operator -
assumption
The one-pass
bag intersection
(∩B) algorithm
- phase 1
The one-pass
bag intersection
(∩B) algorithm
- phase 2
The one-pass
bag intersection
(∩B) algorithm
- phase 1 - example
Phase 1: read S and build search index with (key, #occurence)
The one-pass
bag intersection
(∩B) algorithm
- phase 2 - example
Phase 2: read R and decrement the search key count; output the key if count > 0
The one-pass
bag intersection
(∩B) algorithm
- phase 2 - example
Phase 2: a is not found → discard a
The one-pass
bag intersection
(∩B) algorithm
- phase 2 - example
Phase 2: count(b) = 2 (> 0) → output b and decrement count(b)
The one-pass
bag intersection
(∩B) algorithm
- phase 2 - sample result
Notice the values in the count in the search (hash) table
IO cost and buffer requirement for
∩B
- Bag Intersection operator:
R ∩B S - Example Bag Intersection:
R = {1, 2, 2, 2, 3}; S = {2, 2, 3, 3, 4}; R ∩B S = { 2, 2, 3 }Important observation:
- We need to efficiently identify
the
# occurences of
every tuple
in one of the relations
- We need to build a search structure (hash table or search tree) that contains a count !!!
- We should
index the
smaller relation because:
- This will minimize the size of the search structure !!!
- We need to efficiently identify
the
# occurences of
every tuple
in one of the relations
- One-pass algorithm:
Assumption:
- The relation S is
the
smaller relation
- Building a search structure using S will minimize the memory requirement
Algorithm:
initialize a search structure H on all attributes ; /* ============================================================ Phase 1: Use 1 buffer and scan the SMALLER relation first. Build a search structure on the SMALLER relation The search structure contains a count for each value in S ============================================================ */ while ( S has more data blocks ) { read 1 data block in buffer b; for ( each tuple t ∈ b ) { /* ===================================================== We need a search structure H to implement the test t ∈ H efficiently !!! We can use hash table or some bin. search tree ====================================================== */ if ( t ∉ H ) { insert t in H; initialize count(t) = 1 for H; } else { update count(t) = count(t) + 1 for H; } } } /* =================================================== Now we know how many of each element is in S =================================================== */ /* ============================================================ Phase 2: Use 1 buffer and scan the other relation. Use the search structure to output the common elements, but at most count times !! ============================================================ */ while ( R has more data blocks ) { read 1 data block in buffer b; for ( each tuple t ∈ b ) { /* ===================================================== We use search structure H to implement the test t ∈ H efficiently !!! We can use hash table or some bin. search tree ====================================================== */ if ( t ∈ H ) { if ( count(t) > 0 ) { output( t ); // Because t is in BOTH R and S Update count(t)-- in H; // Use up 1 copy of t ! } } else { // Ignore t, it's not in intersection ! } } }
- The relation S is
the
smaller relation
- Example: Compute
{a, b, b, b, c, d} ∩B
{b, b, c, c, e}
- Phase 1:
make index (with a
count) on the
smaller relation:
- Phase 2 processing:
- Read tuples of
relation R and process:
- Example 1:
processing
a
a is not found in S, so a is not in the intersection !
- Example 2:
processing
b
b is found in S and count > 0; so we have one more copy in the intersection !
We output b and decrement its count by 1 !!!
- Final result:
(after processing all values in
R)
Only 2 copies of b will be outputted because count(b) = 0 will prevent the 3rd copy to be outputted !!!
Only 1 copy of c will be outputted leaving count(c) = 1
- Read tuples of
relation R and process:
- Phase 1:
make index (with a
count) on the
smaller relation:
-
Buffer utilization when
there are M buffers
available:
- Phase 1:
partition the M buffers as
follows:
Use 1 buffer for input from S
Use M−1 buffers for the search structure with a count(t) for each unique element in S
- Phase 2:
partition the M buffers as
follows:
Use 1 buffer for input from R
We are still using M−1 buffers for the search structure in phase 2
- Phase 1:
partition the M buffers as
follows:
- # disk I/O used:
- B(R) + B(S) --- if the relations R and S are clustered
- Memory requirement:
- M (# buffers) ≥ B(S) + 1 buffer