lec 6 External Sorting精选.pptVIP

* 19 External Sorting Jianlin Feng School of Software SUN YAT-SEN UNIVERSITY courtesy of Joe Hellerstein for some slides Why Sort? A classic problem in computer science! Data requested in sorted order e.g., find students in increasing gpa order First step in bulk loading B+ tree index. Useful for eliminating duplicates (Why?) Useful for summarizing groups of tuples Sort-merge join algorithm involves sorting. Problem: sort 100Gb of data with 1Gb of RAM. why not virtual memory? 2-Way Sort: Requires 3 Buffers Pass 0: Read a page, sort it, write it. only one buffer page is used. each sorted page (or subfiles) is called a run. Pass 1, 2, 3, …, etc.: requires 3 buffer pages merge pairs of runs into runs twice as long three buffer pages used. Main memory buffers INPUT 1 INPUT 2 OUTPUT Disk Disk Merging Runs Two-Way External Merge Sort Each pass we read + write each page in file. N pages in the file = the number of passes So total cost is: Idea: Divide and conquer: sort subfiles and merge Input file 1runs 2runs 4runs 8runs PASS 0 PASS 1 PASS 2 PASS 3 9 3,4 6,2 9,4 8,7 5,6 3,1 2 3,4 5,6 2,6 4,9 7,8 1,3 2 2,3 4,6 4,7 8,9 1,3 5,6 2 2,3 4,4 6,7 8,9 1,2 3,5 6 1,2 2,3 3,4 4,5 6,6 7,8 Merging Runs General External Merge Sort To sort a file with N pages using B buffer pages: Pass 0: use B buffer pages. Produce sorted runs of B pages each. Pass 1, 2, …, etc.: merge B-1 runs. B Main memory buffers INPUT 1 INPUT B-1 OUTPUT Disk Disk INPUT 2 . . . . . . . . . More than 3 buffer pages. How can we utilize them? Cost of External Merge Sort Number of passes: Cost = 2N * (# of passes) E.g., with 5 buffer pages, to sort 108 page file: Pass 0: = 22 sorted runs of 5 pages each (last run is only 3 pages) Pass 1: = 6 sorted runs of 20 pages each (last run is only 8 pages) Pass 2: 2 sorted runs, 80 pages and 28 pages Pass 3: Sorted file of 108 pages Formula check: ┌log4 22┐= 3 … + 1 ? 4 passes √ Number of Passes of External Sort (