External Sorting - Home Department of Computer Science外部排序-计算机科学家系.pptVIP

External Sorting Query Processing: Topic 0 Why Sort? A classic problem in computer science! Data requested in sorted order e.g., find students in increasing age order Sorting is first step in bulk loading B+ tree index. Sorting useful for eliminating duplicate copies in a collection of records (Why?) Sort-merge join algorithm involves sorting. Problem: sort 1Gb of data with 32Mb of RAM. why not virtual memory? 2-Way Sort: Requires 3 Buffers Pass 1: read a page, sort it, write it. only one buffer page is used Pass 2, 3, …, etc.: three buffer pages used. 2-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 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 2, …, etc.: merge B-1 runs. 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 Number of Passes of External Sort Internal Sort Algorithm Quicksort is a fast way to sort in memory An alternative is “tournament sort” (a/k/a “heapsort”) Top: Read in B blocks Output: move smallest record to output buffer Read in a new record r insert r into “heap” if r not smallest, then GOTO Output else remove r from “heap” output “heap” in order; GOTO Top More on Heapsort Fact: average length of a run in heapsort is 2B Worst-Case: What is min length of a run? How does this arise? Best-Case: What is max length of a run? How does this arise? Quicksort is faster, but... I/O for External Merge Sort …longer runs often means fewer passes! Actually, do I/O a page at a time In fac