---

Buffer allocation for the zig-zag join

---

• Buffer utilization in a zig-zag join using clustering indexes
  • Suppose both relations R and S has sorted clustering indexes:

    ---

    ---

  • Suppose there are M buffers

    ---

    ---

  • Buffer utilization in the zig-zag join:

    Use 1 buffer to read the index file on relation R Use 1 buffer to read the index file on relation S Use the remaining (M−2) buffers to store joining tuples with the smallest join attribute values Requirement: All joining tuples (from both relations must fit in (M−2) buffers

    Graphically:

  ---

  ---

  ---

  ---

  ---

• Example performance of zig-zag join
  • Consider the following scenario:

    relation R(X,Y) relation S(Y,Z) -------------------------------------------- B(R) = 1000 blks B(S) = 500 blocks T(R) = 10000 tuples T(S) = 5000 tuples R is clustered S is clustered Clustering sorted index on R Clustering sorted index on S V(S,Y) = 100 Available memory: M = 101 buffers

    ---

    ---

  • Buffer utilization:

    1 buffer to read the index of R 1 buffer to read the index of S The remaining (99) buffers to hold tuples with the smallest join values from R and S

    ---

    ---

  • The performance of the zig-zag join algorithm using clustering indexes is:

    Use the clustering index of R and scan R once we will use: B(R) disk I/Os (there is few skipping around because the index is clustering) Use the clustering index of S and scan S once we will use: B(S) disk I/Os (there is few skipping around because the index is clustering) Total # disk IOs = B(R) + B(S) = 1000 + 500 = 1500 blocks

    Note:

    We have assumed that the IO cost for the reading the index is negligible !!!