Consider the two tables above. The first columns are rids and the second columns are the join columns. Assuming that a disk page holds 2 records and the memory has 4 pages: show the detailed working of hash-join ie. how many pages are read and written. Use the hash function "mod 3" for partition phase and "mod 2" for join phase.
Answer is provided below
MY QUESTION IS:
a) For the partition phase, 1 page is allocated for input buffer, and 3 pages for output buffer. How do you determine how many pages should be allocated for each?
b) For join phase, 1 page as input buffer, 1 page for output, and 2 pages for the in-memory hash table. How did they determine how many pages should be allocated for this one as well?
c) The join phase does NOT consider the writing cost. How is this so?
4 4 4 4 Question 2. Consider the two tables above. The first columns are rids and the second columns are the join columns. Assuming that a disk page hold records and the memory has 4 pages. s 2 (1) Show the detailed working of hash-join, i.e., how pages are read and written. Use the hash function "mod 3" for partition phase and "mod 2 for join phase. Answer of Q2: The number of pages: IR-5, ISI-4. The memory size: B-4 (1) Hash-join Partition phase: 1 page as input buffer and 3 pages as output buffer (i.e., hash table). R is partitioned into 3 partitions (note that the last page of partition may have fragments): R1-11,4,4,4,7) (3 pages) R2-12,5,8) (2 pages) RO-13) ( page) S is partitioned into 3 partitions S1-14,73 (1 page) S2-12,5,5,8,8) (3 pages) so-(6) (1 page) Reading cost: RI+IS-5+4-9 Writing cost: 6+5-11 (why writing cost is larger than reading cost?) Join phase: 1 page as input buffer for scanning inner, 1 page as output buffer, and 2 pages for the in-memory hash table for scanning outer. Use "mod 2" for building the hash table R1,S1: build hash table for (smaller) S1 and scan R1 as inner (why not build hash table for R1?) R2,S2: build hash table for (smaller) R2 and scan S2 as inner RO,S0: build hash table for RO and scan S0 as inner Reading cost: 5+6-11 Writing cost: not considered in all join methods. Total cost: 9+11+11-31 4 4 4 4 Question 2. Consider the two tables above. The first columns are rids and the second columns are the join columns. Assuming that a disk page hold records and the memory has 4 pages. s 2 (1) Show the detailed working of hash-join, i.e., how pages are read and written. Use the hash function "mod 3" for partition phase and "mod 2 for join phase. Answer of Q2: The number of pages: IR-5, ISI-4. The memory size: B-4 (1) Hash-join Partition phase: 1 page as input buffer and 3 pages as output buffer (i.e., hash table). R is partitioned into 3 partitions (note that the last page of partition may have fragments): R1-11,4,4,4,7) (3 pages) R2-12,5,8) (2 pages) RO-13) ( page) S is partitioned into 3 partitions S1-14,73 (1 page) S2-12,5,5,8,8) (3 pages) so-(6) (1 page) Reading cost: RI+IS-5+4-9 Writing cost: 6+5-11 (why writing cost is larger than reading cost?) Join phase: 1 page as input buffer for scanning inner, 1 page as output buffer, and 2 pages for the in-memory hash table for scanning outer. Use "mod 2" for building the hash table R1,S1: build hash table for (smaller) S1 and scan R1 as inner (why not build hash table for R1?) R2,S2: build hash table for (smaller) R2 and scan S2 as inner RO,S0: build hash table for RO and scan S0 as inner Reading cost: 5+6-11 Writing cost: not considered in all join methods. Total cost: 9+11+11-31
