数据库课件lec6ExternalSorting幻灯片.pptVIP

* 19 * 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? Streaming Data Through RAM An important method for sorting other DB operations Simple case: Compute f(x) for each record, write out the result Read a page from INPUT to Input Buffer Write f(x) for each item to Output Buffer When Input Buffer is consumed, read another page When Output Buffer fills, write it to OUTPUT Reads and Writes are not coordinated E.g., if f() is Compress(), you read many pages per write. E.g., if f() is DeCompress(), you write many pages per read. f(x) RAM Input Buffer Output Buffer OUTPUT INPUT 2-Way Sort: Requires 3 Buffers Pass 0: Read a page, sort it, write it. only one buffer page is used (as in previous slide) 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