---

One-pass Algorithm for δ

---

---

---

 

Slideshow:

The duplicate elimination (δ) operator

The one-pass duplicate elimination (δ) algorithm

The one-pass duplicate elimination (δ) algorithm - Example
 
Initial state: (hash table/binary search tree structure is empty)

 
The one-pass duplicate elimination (δ) algorithm - Example
 
Process next block of data: (read disk block into buffer)

 
The one-pass duplicate elimination (δ) algorithm - Example
 
Process next block of data: (process first tuple)

Find(a): not found  →  insert(a) in search structure and output(a)
The one-pass duplicate elimination (δ) algorithm - Example
 
Process next block of data: (process second tuple)

Find(b): not found  →  insert(b) in search structure and output(b)
The one-pass duplicate elimination (δ) algorithm - Example
 
Process next block of data: (process second tuple)

Find(a): found !!  →  discard(a)
Buffer utilization
How to assign buffers when there are M buffers available:
 
IO cost and buffer requirement for δ
   
Note: V(R, [A1, A2, .., An]) = # distinct values in the attributes [A1, A2, .., An]
Note: the DBMS maintains various statistics on the relations (= data) to help estimate costs and constraint requirements
❮ ❯



---

---

---

---

• Duplicate Elimination δ
  • Duplicate Elimination operator:

    δ ( R )

  • Example Duplicate Elimination:

    R = { a, b, a, c, a, b } δ ( R ) = {a, b, c}

    Important observation:

    We need to efficiently identify the tuples that we have seen previous Solution: We build a search structure (i.e.: a hash table or a search tree) !!!

    ---

    ---

  • One-pass algorithm:

    initialize a search structure H on all attributes of R; while ( R 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 ) { discard t // duplicate !!! } else { insert t in H; // Help find duplicates move t to output // This is the first occurence } } }

    ---

    ---

  • Example:

    Eliminate duplicates on the 6 tuples {a, b, a, a, b, c}: Read in one data block: Process first tuple a: a not found in hash table: store a in hash table and output a Process next tuple b: b not found in hash table: store b in hash table and output b Process next tuple a: a is found in hash table: discard a !!! And so on...

  ---

  ---

  ---

  ---

• How to use the buffers

  • Buffer utilization when there are M buffers available:

    ---

    ---

  • Reason:

    You only need to use 1 buffer to read in the tuples You will need many buffers to build/store the search structure (e.g., hash table) Therefore: Use the remaining (M−1) buffer to store the search structure !!!

  ---

  ---

  ---

  ---

• Cost Analysis for δ
  • # disk I/O used:

    B(R) --- if the relation R is clustered T(R) --- if the relation R is UNclustered

    ---

    ---

  • Memory requirement:

    The search index size must be contained in M − 1 buffers: V(R, [A1, A2, .., An]) × (size of 1 tuple) ≤ (M − 1) blocks A1, A2, .., An are the projection attributes (Recall that: 1 block is stored in 1 buffer)

    Recall:

    V(R, [A1, A2, .., An]) = # distinct values that [A1, A2, .., An] can assume V(R, [A1, A2, .., An]) = | δ ( π A1, A2, .., An ( R ) ) |

    ---

    ---

  • Note:

    DBMS usually maintain statistical information on the stored relations One of these statistics is the number of tuples and histograms on the range of values We can use these statistics to estimate the value of   V(R, [A1, A2, .., An]) (discussed later)