---

A summary and performance comparison of some algorithms for σ_A=c(R)

---

---

---

 

Slideshow:

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:
 
Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:
 
R is clustered      and      R has no index on attribute A

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:
 
R is not clustered      and      R has no index on attribute A

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:
 
R is clustered      and      R has clustering index on attribute A

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:

Comparing the performance of some algorithms for σ_A=c(R)
Statistics on the input relation:
 
R is clustered/unclustered      and      R has no index on attribute A
Assumption: average 1 tuple with "A=c" found in 1 block

Comparing the performance of some algorithms for σ_A=c(R)

❮ ❯



---

---

---

---

• Example performance comparision
  • Suppose he Relation R has:

    B(R) = 1000 blocks T(R) = 20,000 tuples V(R,A) = 100 different values

    ---

    ---

  • Case 1:

    R is clustered R has no index on attribute A

    Cost of processing σ_A=c(R):

    Without an index, we must scan the entire relation: # disk IO = B(R) = 1000 blocks

    ---

    ---

  • Case 2:

    R is not clustered R has no index on attribute A

    Cost of processing σ_A=c(R):

    Without an index, we must access every tuple that is stored in a different block: # disk IO = T(R) blocks // T(R) = # tuples in R = 20000 blocks

    ---

    ---

  • Case 3:

    R has clustering index on attribute A In this case, R must be clustered too !!! (Otherwise, the index can never be clustering) Graphically:

    Cost of processing σ_A=c(R):

    1 # disk IO = ------- B(R) V(R,A) = 1000/100 = 10 blocks (see analysis in: click here)

    (We assumed that the cost of reading the (smaller) index (file) is negligible)

    ---

    ---

  • Case 4:

    R has NON-clustering index on attribute A The relation (data file) R can be clustered or unclustered !!! This fact will not matter since we assumed that different tuples are stored in different blocks Graphically:

    Cost of processing σ_A=c(R):

    1 # disk IO = ------- T(R) V(R,A) = 20000/100 = 200 blocks (see analysis in: click here)

    (We assumed that the cost of reading the (smaller) index (file) is negligible)

    ---

    ---

  • Case 5:

    A is a key attribute in relation R There is an index on A (clustering/non-clustering does not matter)

    Cost of processing σ_A=c(R):

    # disk IO = 1 block (there is only 1 tuple with value c)