13. External Sorting.ppt

13. External Sorting Motivation 2-way External Sort: Memory, passes,cost General External Sort: Memory, passes, cost Optimizations Snowplow Double Buffering Forecasting Using a B+ tree index Bucket Sort Intergalactic Standard Reference Graefe, Implementing Sorting in Database Systems /citation.cfm?id=1132964 Learning Objectives Derive formula for cost of external merge sort Derive amount of memory needed to sort a file in 2 passes, using merge or bucket sort Describe algorithm for generating longer initial runs and identify its best and worst cases Describe forecasting and why it is useful Identify when indexes should be used for sorting Identify the pros and cons of external bucket vs merge sort. Why sort? A classic problem in computer science! Exercises many software and hardware features Data is often requested in sorted order e.g., find students in increasing gpa order Sorting is first step in bulk loading B+ tree index. Sorting useful for some query processing algoritms (Chapter 14) Problem: sort 1Gb of data with 1Mb of RAM. why not virtual memory? Sort algorithms? If the data can fit in memory, which sort algorithm is best? But most DBMS files will not fit in available memory If the data is larger than memory, try the same alg. Suppose for this data, your sort alg. requires 220 random memory accesses Memory access takes 1 microsec, disk takes 10 millisecs. How much time is required to do your sort algorithm’s memory accesses? If there is enough memory to hold the data? If the data is four times the size of memory? External Sorts Definition: When data is larger than memory. An aside: What is “Memory”? Physical memory? The DBMS is not the only player We concluded that most in-memory sort algs won’t be effective for external sorting. What sort algorithms are best for external sort? Sort-based Hash-based 2-Way External Merge Sort: Memory? Pass 0: Read a page, sort it, write it. How many buffer pages needed? Pass 1, 2, 3, …, etc.: How many buffer pages needed? 2