Homework 2 Question 5 Suppose we are given a database with the following schema.
ID: 3577701 • Letter: H
Question
Homework 2
Question 5
Suppose we are given a database with the following schema. Users User IDINTEGER, Name CHAR(30), Age INTEGER, ReviewCount INTEGER) Businesses (BusinessID INTEGER, BName CHAR(30), City CHAR(20), State CHAR(2) Checkins (BusinessIDINTEGER, Weekdays INTEGER, Weekends INTEGER) Reviews (ReviewID INTEGER, UserIDINTEGER, BusinessID INTEGER, Stars REAL) Reviews (UserID) is a foreign key referring to Users (UserID). Reviews (BusinessID) is a foreign key referring to Businesses (BusinessID). Checkins (BusinessID) is a foreign key referring to Businesses (BusinessID) A page is 8 kB in size. The RDBMS buffer pool has 10,000 pages, all of which are usable. Initially, the buffer pool is empty. The relation instances have the following statistics. Assume there are no NULL values. Each integer or real is 8B, and each character is 1B (so as an example CHAR(20) is 20B). Additionally, the record id of each tuple is 8B. Relation Number of Pages Number of Tuples 75,684 10m. Users 41,504 5mm Businesses 19,532 5mm Checkins Reviews 488,282 100mExplanation / Answer
You can do a hash join between Users and Reviews since there is a equi join predicate. Here the smaller of two data sets to build a hash table on the join key in memory, using a deterministic hash function to specify the location in the hash table in which to store each row. The database then scans the larger data set, probing the hash table to find the rows that meet the join condition. A hash join is most cost effective when the smaller data set fits in memory. In this case, the cost is limited to a single read pass over the two data sets.
After this pipeline the result to the selection, and finally pipeline the result of the selection to an aggregation operator (which is count).
The cost of the plan will then be cost of the hash join. The partition definitely fits in memory (since B is much much bigger compared to 1.4 × 75, 684), so the I/O cost will be 3 × (75, 684 + 488, 282) = 1, 691, 898.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.