Overview of Query Evaluation.pptVIP

8 8 8 11 12 11 11 11 12 12 12 13 13 14 14 14 16 15 19 Observation on Hash Join Overflow If the hash function does not partition uniformly, one or more R partitions may not fit in memory. Significantly degrade the performance. Can apply hash-join technique recursively to do the join of this overflow R-partition with corresponding S-partition. Hybrid Hash-Join Idea: Do not write one of the partitions of R and S to disk. When is it possible? We can keep one of the partitions of the smaller relation always in memory. B = f * M/k (buffers for keeping a partition) + (k – 1) (keep 1 page in buffer for each of the remaining partitions) + 1 (1 page in buffer for reading in S (or later R)) + 1 (1 output page when reading in R) Remember: k = number of partitions i.e., (B – (k + 1)) = f * M/k Choose such an appropriate k (or number of partitions) Hybrid Hash-Join (contd) How to perform Hybrid Hash-Join? Partitioning S is done as: Build an in-memory hash table for the first partition of S during the partitioning phase. Other partitions keep 1 page in buffer and write to disk when needed. 1 buffer page for reading in S Partitioning R is done as: If a tuple hashes to the partition corresponding to the in-memory partition of S, then join and output tuples If a tuple hashes to any of the remaining (k – 1) partitions, write it to the buffer page (and write this buffer page to disk as needed) 1 buffer page for reading in R; 1 buffer page for output Remaining partitions of R and S are done as usual Saving: avoid writing the first partitions of R and S to disk. E.g. R = 500 pages, S=1000 pages B = 300 (We make 2 partitions)partition phase: scan R and write one partition out. 500 + 250 scan S and write out one partition. 1000 + 500probing phase: only second partition is scaned: 250+500 Total = 3000 ( Hash Join will take 4500 ) Hash-Join vs. Block Nested Join If hash table for entire smaller relation fits in memory, equal. Otherwi