why sort
play

Why Sort? Used for eliminating duplicates Select DISTINCT - PDF document

Dec 28, 2022 •323 likes •412 views

Why Sort? Used for eliminating duplicates Select DISTINCT External Sorting Bulk loading B+ trees Need to first sort leaf level pages Data requested in sorted order S.name SELECT Sailor S FROM ORDER BY S.age Some

  1. Why Sort? � Used for eliminating duplicates � Select DISTINCT … External Sorting � Bulk loading B+ trees � Need to first sort leaf level pages � Data requested in sorted order S.name � SELECT Sailor S FROM ORDER BY S.age � Some join algorithms use sorting � Sort-merge join Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 Sorting: Main Challenge 2-Way External Merge Sort � Phase 1: Read a page at a time, sort it, write it � Sort 1 TB of data with 1 GB of RAM � Only one buffer page used � Why not just use QuickSort? (i.e., simply map disk pages to virtual memory) 1 Page Main memory buffers Disk Disk Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 Two-Way External Merge Sort: Phase 1 2-Way External Merge Sort � Phase 2: Make multiple passes to merge runs Input file 3,4 6,2 9,4 8,7 5,6 3,1 2 � Pass 1: Merge two runs of length 1 (page) PHASE 1 1-page runs 3,4 2,6 4,9 7,8 5,6 1,3 2 � Pass 2: Merge two runs of length 2 (pages) � … until 1 run of length N � Three buffer pages used � Assume input file with N data pages � What is the cost of Phase 1 (in terms of # I/Os)? INPUT 1 OUTPUT INPUT 2 Main memory buffers Disk Disk Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6

  2. 2-Way External Merge Sort 2-Way External Merge Sort: Analysis 6,2 2 Input file 3,4 9,4 8,7 5,6 3,1 PHASE 1 � Total I/O cost for sorting file with N pages 1,3 2 1-page runs 3,4 2,6 4,9 7,8 5,6 PASS 1 4,7 1,3 2,3 2-page runs 8,9 5,6 2 4,6 N PASS 2 � Cost of Phase 1 = 2 2,3 4,4 1,2 4-page runs 6,7 3,5 � N � PHASE 2 log � Number of passes in Phase 2 = 6 8,9 2 PASS 3 N 2 � Cost of each pass in Phase 2 = 1,2 × N � N � � Cost of Phase 2 = 2,3 2 log 2 3,4 8-page runs ( ) 4,5 � � + 6,6 N N 2 log 1 � Total cost = 7,8 2 9 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8 General External Merge Sort: 2-Way External Merge Sort Motivation � Phase 1: Read a page at a time, sort it, write it � Only one buffer page used � 2-Way merge sort uses at most 3 buffer pages � How can this be modified if B buffer pages � What if more buffer pages were available? are available? � Can we use these extra buffer pages to reduce sorting cost? � Specifically, how would Phases 1 and 2 change? 1 Page Main memory buffers Disk Disk Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10 General External Merge Sort General External Merge Sort: Phase 1 � Phase 1: Read B pages at a time, sort B pages in main � # buffer pages B = 4 memory, and write out B pages � Length of each run = B pages � Assuming N input pages, number of runs = N/B N 2 � Cost of Phase 1 = Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 8,9 6,9 6,8 4-page runs 6,7 5,6 5,5 B Pages 4,4 2,3 3,4 2,3 2,3 1,1 Main memory buffers Disk Disk Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12

  3. 2-Way External Merge Sort General External Merge Sort � Phase 2: Make multiple passes to merge runs � Phase 2: Make multiple passes to merge runs � Pass 1: Merge two runs of length 1 (page) � Pass 1: Produce runs of length B(B-1) pages � Pass 2: Merge two runs of length 2 (pages) � Pass 2: Produce runs of length B(B-1) 2 pages � … until 1 run of length N � … � Three buffer pages used � Pass P: Produce runs of length B(B-1) P pages � How can this be modified if B buffer pages available? INPUT 1 INPUT 1 . . . INPUT 2 . . . . . . OUTPUT OUTPUT INPUT 2 INPUT B-1 Disk Main memory buffers Disk Disk B Main memory buffers Disk Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 2,3 2,3 1,1 Pass 1 Pass 1 Main Memory Main Memory Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 15 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 16 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 2,3 2,3 1,1 2,3 1,1 2,3 Pass 1 Pass 1 Main Memory Main Memory Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 17 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 18

  4. General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 8,9 6,8 8,9 6,8 6,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 2,3 2,3 1 2,3 2,3 Pass 1 Pass 1 1 1,1 Main Memory Main Memory Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 19 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 20 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 2,3 2,3 2,3 2,3 2,3 Pass 1 Pass 1 Main Memory Main Memory 1,1 1,1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 21 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 22 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 2,3 3 2,3 2,3 2,3 2,3 Pass 1 Pass 1 2 Main Memory Main Memory 1,1 1,1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 23 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 24

  5. General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 8,9 6,8 8,9 6,8 6,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 3 3 3 2,3 3 2,3 Pass 1 Pass 1 2,2 2,2 Main Memory Main Memory 1,1 1,1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 25 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 26 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 3 3 3 3 3 Pass 1 Pass 1 2 2,3 2,2 2,2 Main Memory Main Memory 1,1 1,1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 27 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 28 General External Merge Sort: Phase 2 General External Merge Sort: Phase 2 � # buffer pages B = 4 � # buffer pages B = 4 Input file Input file 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 3,4 6,2 9,4 8,7 5,6 3,1 9,2 6,1 8,2 3,4 5,5 6,3 Phase 1 Phase 1 6,8 6,8 8,9 6,9 8,9 6,9 4-page runs 4-page runs 6,7 5,6 5,5 6,7 5,6 5,5 4,4 2,3 3,4 4,4 2,3 3,4 2,3 2,3 2,3 2,3 1,1 1,1 Phase 2 Phase 2 4,4 3 3 3 3 Pass 1 Pass 1 2,3 2,3 2,2 2,2 Main Memory Main Memory 1,1 1,1 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 29 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 30

Recommend

R A D I X S O R T Radix Sort 147 dnc CS 16: Radix Sort Radix Sort Unlike other sorting

R A D I X S O R T Radix Sort 147 dnc CS 16: Radix Sort Radix Sort Unlike other sorting

CS 16: Radix Sort R O A D S T R I X R A D I X S O R T Radix Sort 147 dnc CS 16: Radix Sort Radix Sort Unlike other sorting methods, radix sort considers the structure of the keys Assume keys are represented in a base M number system

464 views • 15 slides
Sorting Lower Bound Radix Sort Radix sort to the rescue sort of

Sorting Lower Bound Radix Sort Radix sort to the rescue sort of

What is the min height of a tree with X external nodes? Sorting Lower Bound Radix Sort Radix sort to the rescue sort of http://www.cs.auckland.ac.nz/software/AlgAnim/radixsort.html EditorTree evals due last night late is better

554 views • 14 slides
SORTING Chapter 8 Sorting 2 Why sort? To make searching faster! How? Binary Search gives

SORTING Chapter 8 Sorting 2 Why sort? To make searching faster! How? Binary Search gives

11/14/2017 SORTING Chapter 8 Sorting 2 Why sort? To make searching faster! How? Binary Search gives log(n) performance. There are many algorithms for sorting: bubble sort, selection sort, insertion sort, quick sort, heap sort, Why so

463 views • 15 slides
Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should

Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should

2/12/2018 What is the min height of a tree with X external nodes? Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be able to explain why comparison-based sorts need at least O(n log n)

316 views • 7 slides
Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be

Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be

What is the min height of a tree with X external nodes? Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be able to explain why comparison-based sorts need at least O(n log n) time

416 views • 15 slides
Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be

Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be

What is the min height of a tree with X external nodes? Sorting Lower Bound Radix Sort Radix sort to the rescue sort of After today, you should be able to explain why comparison-based sorts need at least O(n log n) time

445 views • 17 slides
Sort Sort and n 0 such that g( n ) c f( n ) for all n n 0 . Procedures Procedures If

Sort Sort and n 0 such that g( n ) c f( n ) for all n n 0 . Procedures Procedures If

One-Slide Summary g is in O(f) iff there exist positive constants c Sort Sort and n 0 such that g( n ) c f( n ) for all n n 0 . Procedures Procedures If g is in O(f) we say that f is an upper bound for g. and and We use

270 views • 9 slides
Merge sort M ERGE -S ORT A [1 . . n ] 1. If n = 1, done. 2. Recursively sort A [ 1 . . n /2

Merge sort M ERGE -S ORT A [1 . . n ] 1. If n = 1, done. 2. Recursively sort A [ 1 . . n /2

CS 3343 -- Spring 2009 Merge sort M ERGE -S ORT A [1 . . n ] 1. If n = 1, done. 2. Recursively sort A [ 1 . . n /2 ] and A [ n /2 +1 . . n ] . 3. Merge the 2 sorted lists. Merge Sort Key subroutine: M ERGE Carola Wenk

467 views • 8 slides
Sorting Chapter 7 1 Quick Sort One of the most popular fast sorting algorithms Quick sort

Sorting Chapter 7 1 Quick Sort One of the most popular fast sorting algorithms Quick sort

Sorting Chapter 7 1 Quick Sort One of the most popular fast sorting algorithms Quick sort overcomes the drawback of merge sort of creating an additional array Generally, quick sort is the most efficient algorithm for large arrays 2 Quick

548 views • 35 slides
Topological Sort Shivam Patel Viktor Zenkov Questions 1. Who first described topological sort?

Topological Sort Shivam Patel Viktor Zenkov Questions 1. Who first described topological sort?

Topological Sort Shivam Patel Viktor Zenkov Questions 1. Who first described topological sort? 2. What kind of graph is required to perform a topological sort? 3. What is the running time of topological sort? Outline About Us

598 views • 33 slides
Sort Algorithms 15-110 - Friday 10/09 Learning Objectives Recognize the general algorithm and

Sort Algorithms 15-110 - Friday 10/09 Learning Objectives Recognize the general algorithm and

Sort Algorithms 15-110 - Friday 10/09 Learning Objectives Recognize the general algorithm and trace code for three algorithms: selection sort , insertion sort , and merge sort Compute the Big-O runtimes of selection sort, insertion sort,

818 views • 37 slides
Bucket-Sort and Radix-Sort 1, c 3, a 3, b 7, d 7, g 7, e B 0 1

Bucket-Sort and Radix-Sort 1, c 3, a 3, b 7, d 7, g 7, e B 0 1

Bucket-Sort and Radix-Sort 1, c 3, a 3, b 7, d 7, g 7, e B 0 1 2 3 4 5 6 7 8 9 Bucket-Sort and Radix-Sort 1 Bucket-Sort (10.5.1) Let be S be a sequence of n Algorithm bucketSort ( S, N ) (key,

404 views • 9 slides
Merge Sort 7 2 9 4 2 4 7 9 7 2 2 7 9 4 4 9 7 7 2 2 9 9 4

Merge Sort 7 2 9 4 2 4 7 9 7 2 2 7 9 4 4 9 7 7 2 2 9 9 4

Merge Sort 7 2 9 4 2 4 7 9 7 2 2 7 9 4 4 9 7 7 2 2 9 9 4 4 Sets 1 Outline and Reading Divide-and-conquer paradigm (10.1.1) Merge-sort (10.1) Algorithm Merging two sorted sequences

1.08k views • 64 slides
How fast can we sort? All the sorting algorithms we have seen so far are comparison sorts : only

How fast can we sort? All the sorting algorithms we have seen so far are comparison sorts : only

CS 3343 -- Spring 2009 How fast can we sort? All the sorting algorithms we have seen so far are comparison sorts : only use comparisons to determine the relative order of elements. E.g ., insertion sort, merge sort, quicksort, heapsort. The

290 views • 14 slides
Lists Weiss sec. 6.5 (sort of) ch. 17 (sort of) Arrays Random access a[38] gets 39 th

Lists Weiss sec. 6.5 (sort of) ch. 17 (sort of) Arrays Random access a[38] gets 39 th

Lists Weiss sec. 6.5 (sort of) ch. 17 (sort of) Arrays Random access a[38] gets 39 th element But fixed size, specified at construction e.g. pickLineFromFile ( ) in Assignment01 One approach: java.util.ArrayList Keep an array

363 views • 24 slides
2-Way Sort Problem You have some large number (e.g., 3072) pages of data to sort You only have a

2-Way Sort Problem You have some large number (e.g., 3072) pages of data to sort You only have a

2-Way Sort Problem You have some large number (e.g., 3072) pages of data to sort You only have a small number (e.g., 3) pages to do it How do you do this? Idea 1: Sort/Merge Phase 1: Load 3 pages of data Sort everything Flush out this new

342 views • 6 slides
EE 355 Unit 18 DFS and Topological Sort Mark Redekopp 2 Topological Sort Given a graph of

EE 355 Unit 18 DFS and Topological Sort Mark Redekopp 2 Topological Sort Given a graph of

1 EE 355 Unit 18 DFS and Topological Sort Mark Redekopp 2 Topological Sort Given a graph of dependencies (tasks, prerequisities, etc.) EE 109L MA 245 PHYS 152 topological sort creates a consistent ordering of tasks EE 209 EE 202L

1.16k views • 60 slides
Walk through previous lecture Working with Images PIL Bubble Sort and Selection Sort Fibonacci

Walk through previous lecture Working with Images PIL Bubble Sort and Selection Sort Fibonacci

Walk through previous lecture Working with Images PIL Bubble Sort and Selection Sort Fibonacci Series Recursion Iteration Running times Insertion Sort Pseudocode: for i from 1 to length[A]-1 do value := A[i] j := i-1 while j >= 0 and

314 views • 17 slides
Sorting a List: bubble sort selection sort insertion sort Sept. 22, 2017 1 Sorting BEFORE

Sorting a List: bubble sort selection sort insertion sort Sept. 22, 2017 1 Sorting BEFORE

COMP 250 Lecture 7 Sorting a List: bubble sort selection sort insertion sort Sept. 22, 2017 1 Sorting BEFORE AFTER 3 -5 17 -2 -5 3 -2 4 23 17 4 23 2 Example: sorting exams by last name 3 Sorting Algorithms Bubble sort

686 views • 35 slides
Bubble Sort! Tyralyn Tran What is a bubble sort?!?!?!?!?!?!?!? In a bubble sorting algorithm,

Bubble Sort! Tyralyn Tran What is a bubble sort?!?!?!?!?!?!?!? In a bubble sorting algorithm,

http://www.internationalhero.co.uk/p/powpuff1.jpg http://www.skorks.com/wp- content/uploads/2010/05/sort.jpg Bubble Sort! Tyralyn Tran What is a bubble sort?!?!?!?!?!?!?!? In a bubble sorting algorithm, the elements of the list

337 views • 20 slides
SOME MORE DECREASE AND CONQUER ALGORITHMS Insertion Sort on Steroids SHELL'S SORT A

SOME MORE DECREASE AND CONQUER ALGORITHMS Insertion Sort on Steroids SHELL'S SORT A

What questions do you have? Decrease by a constant factor Decrease by a variable amount SOME MORE DECREASE AND CONQUER ALGORITHMS Insertion Sort on Steroids SHELL'S SORT A QUICK RECAP 1 Shell's Sort We use the following gaps: 7,

115 views • 8 slides
Overview/Questions Review: selection sort and bubble sort On what basis should we compare

Overview/Questions Review: selection sort and bubble sort On what basis should we compare

CS101 Lecture 31: Sorting Algorithms Quick Sort Aaron Stevens 17 April 2009 1 Overview/Questions Review: selection sort and bubble sort On what basis should we compare algorithms? How else can we approach sorting? A divide

299 views • 16 slides
Sort Sort If g is in O(f) we say that f is an upper bound Procedures Procedures for g. and

Sort Sort If g is in O(f) we say that f is an upper bound Procedures Procedures for g. and

One-Slide Summary g is in O(f) iff there exist positive constants c and n 0 such that g( n ) c f( n ) for all n n 0 . Sort Sort If g is in O(f) we say that f is an upper bound Procedures Procedures for g. and and We use

629 views • 6 slides
Engineering a Sort Function Engineering a Sort Function JON L. BENTLEY M. DOUGLAS McILROY

Engineering a Sort Function Engineering a Sort Function JON L. BENTLEY M. DOUGLAS McILROY

Bentley and MacIlroy, 1993 Engineering a Sort Function Engineering a Sort Function JON L. BENTLEY M. DOUGLAS McILROY AT&T Bell Laboratories, 600 Mountain Avenue, Murray Hill, NJ 07974, U.S.A. Jim Royer SUMMARY CIS 351 We recount the

298 views • 5 slides

More recommend