[solved]-Assume Given Query Tree Bank Database Statistics Given Class 1 Cpu Cost First Sl Cost Jn I Q38997738

Assume the given query tree for the bank database and the statistics given in class. 1. What is the CPU cost of doing the first SL and the cost of the JN if the index on the column bname of the account table is NOT clustered? 2. What is the CPU cost of doing the first SL and the cost of the JN if the index on the column bname of the account table is clustered? 3. We have a 3.page buffer. For each of the above two cases, what is the total 10 cost of the tree if a. The 2 SL is done on-the-fly b. The 2 SL is done on a temptable. PJ SL cely JN SL Customer Index on chame Account Index on bname Bank Database Statistics • Statistics about this database: • Database page size is 4Kbytes (4*1024 = 4096 bytes). • The page overhead is 96 bytes • This leaves 4,000 bytes as usable bytes to load table rows Account table: • There are 100,000 Accounts – 100,000 rows • Each row is 40 bytes long • Each page can hold 4,000/40 = 100 Account rows • Therefore, there are 100,000/100 = 1000 pages in the Account table • Customer table: • There are 40,000 Customers – 40,000 rows • Each row is 50 bytes long • Each page can hold 4,000/50 = 80 Customer rows • Therefore, there are 40,000/80 = 500 pages in the Customer table • Branch table: • There are 100 branches • Each row is 80 bytes long • Each page can hold 4,000/80 = 50 Branch rows • Therefore, there are 100/50 = 2 pages in the Branch table • Customers are rated from 1 to 10 • There are 100 branches in the Bank • We will assume uniform distribution • On the average, there are 2.5 accounts per customer • On the average, there are 1000 accounts per branch Show transcribed image text Assume the given query tree for the bank database and the statistics given in class. 1. What is the CPU cost of doing the first SL and the cost of the JN if the index on the column bname of the account table is NOT clustered? 2. What is the CPU cost of doing the first SL and the cost of the JN if the index on the column bname of the account table is clustered? 3. We have a 3.page buffer. For each of the above two cases, what is the total 10 cost of the tree if a. The 2 SL is done on-the-fly b. The 2 SL is done on a temptable. PJ SL cely JN SL Customer Index on chame Account Index on bname
Bank Database Statistics • Statistics about this database: • Database page size is 4Kbytes (4*1024 = 4096 bytes). • The page overhead is 96 bytes • This leaves 4,000 bytes as usable bytes to load table rows Account table: • There are 100,000 Accounts – 100,000 rows • Each row is 40 bytes long • Each page can hold 4,000/40 = 100 Account rows • Therefore, there are 100,000/100 = 1000 pages in the Account table • Customer table: • There are 40,000 Customers – 40,000 rows • Each row is 50 bytes long • Each page can hold 4,000/50 = 80 Customer rows • Therefore, there are 40,000/80 = 500 pages in the Customer table • Branch table: • There are 100 branches • Each row is 80 bytes long • Each page can hold 4,000/80 = 50 Branch rows • Therefore, there are 100/50 = 2 pages in the Branch table • Customers are rated from 1 to 10 • There are 100 branches in the Bank • We will assume uniform distribution • On the average, there are 2.5 accounts per customer • On the average, there are 1000 accounts per branch

Expert Answer

---

Answer to Assume the given query tree for the bank database and the statistics given in class. 1. What is the CPU cost of doing th… . . .