DatabaseDesignandTheEntity-RelationshipModel.ppt

Database Design andThe Entity-Relationship Model Lecture 21 RG - Chapter 2 Administriva Homework 4 Due One Week From Today Midterm is graded Exam Summary Average: 74.5 Max: 93.5 Min: 36.5 Std Dev: 11.6 Exam Details Section: Low – High / Total (Average) 1: External Sorting 10 – 24 / 24 (18) 2: Query Execution 17 – 43 / 44 (36) 3: Query Optim. 6.5 – 29 / 32 (20) Section 1: External Sorting For all questions in this section, assume that you have 96 pages in the buffer pool, and you wish to sort EMP by Ename. Using the general external sort, how many sorting passes are required? (6pts) #passes = 1 + Ceil(log(B-1)(N/B)) = 1 + Ceil(log(95)(10,000/96)) = 1 + 2 = 3 What is the I/O cost of doing this sort? (6pts) I/O Cost = 2N * (# passes) = 20,000 * 3 Section 1 (cont) Database systems sometimes use “blocked I/O” because reading a block of continuous pages is more efficient that doing a separate I/O for each page. Assume that in our computer system, it is much more efficient to read and write blocks of 32 pages at a time, so all reads and writes from files must be in blocks of 32 pages (and if a file has less than 32 pages, it is padded with blank pages). Consider doing external sort with blocked I/O, which gets faster I/O at the expense of more sorting passes. If the database must read and write 32 pages at a time, how many sorting passes are required? Show either the formula you use, or the temp file sizes after each pass. (4pts) Pass 0: 105 runs of 96 pages Pass 1: 2-way merge 96 + 96 - result is 53 runs of 192 pages Pass 2: 2-way merge of 192 + 192 - result is 27 runs of 384 pages Pass 3: - result is 14 runs of 768 pages, Pass 4: - result is 7 runs 1536 pages, Pass 5: - result is 4 runs 3072 pages, Pass 6: - result is 2 runs of 6144, Pass 7: - result is 10,000 8 passes total Section 1 (cont) Blocked I/O is used because reading a 32block is faster than 32 separate 1I/Os. For each sorting pass, you still must read and write every page in the file, but