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

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

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
Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos A.

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos A.

Faloutsos/Pavlo CMU - 15-415/615 CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos A. Pavlo Lecture#12: External Sorting CMU SCS Today's Class Sorting Overview Two-way Merge Sort

450 views • 16 slides
Distributed Databases: Design and Query Execution Data Fragmentation and Placement

Distributed Databases: Design and Query Execution Data Fragmentation and Placement

Distributed Databases: Design and Query Execution Data Fragmentation and Placement Fragmentation: How to split up the data into smaller fragments? Horizontal Vertical Placement: Where to put the fragments? COMP-577:

422 views • 18 slides
THE NOSQL MOUVEMENT GENOVEVA VARGAS SOLAR FRENCH COUNCIL OF SCIENTIFIC RESEARCH, LIG-LAFMIA,

THE NOSQL MOUVEMENT GENOVEVA VARGAS SOLAR FRENCH COUNCIL OF SCIENTIFIC RESEARCH, LIG-LAFMIA,

THE NOSQL MOUVEMENT GENOVEVA VARGAS SOLAR FRENCH COUNCIL OF SCIENTIFIC RESEARCH, LIG-LAFMIA, FRANCE Genoveva.Vargas@imag.fr http://www.vargas-solar.com/bigdata-managment STORING AND ACCESSING HUGE AMOUNTS OF DATA Yota 10 24 Cloud Zetta 10 21

582 views • 18 slides
Lecture 7: Indexes and Database Tuning Wednesday, November 10, 2010 Dan Suciu -- CSEP544 Fall 1

Lecture 7: Indexes and Database Tuning Wednesday, November 10, 2010 Dan Suciu -- CSEP544 Fall 1

Lecture 7: Indexes and Database Tuning Wednesday, November 10, 2010 Dan Suciu -- CSEP544 Fall 1 2010 The Take-home Final Poll: no date is good for everyone Will settle for maximum flexibility Main take-home final December 4 and

1.72k views • 143 slides
on Many-Core GPUs Xiaochun Ye, Dongrui Fan, Wei Lin, Nan Yuan, and Paolo Ienne Key Laboratory of

on Many-Core GPUs Xiaochun Ye, Dongrui Fan, Wei Lin, Nan Yuan, and Paolo Ienne Key Laboratory of

High Performance Comparison-Based Sorting Algorithm on Many-Core GPUs Xiaochun Ye, Dongrui Fan, Wei Lin, Nan Yuan, and Paolo Ienne Key Laboratory of Computer System and Architecture ICT, CAS, China Outline GPU computation model Our sorting

231 views • 19 slides
Chapter 7 External Sorting Sorting Tables Larger Than Main Memory Query Processing Sorting

Chapter 7 External Sorting Sorting Tables Larger Than Main Memory Query Processing Sorting

External Sorting Torsten Grust Chapter 7 External Sorting Sorting Tables Larger Than Main Memory Query Processing Sorting Two-Way Merge Sort Architecture and Implementation of Database Systems External Merge Sort Summer 2016 Comparisons

660 views • 29 slides
Comparison Sorting Alphabetical list of people List of countries ordered by population

Comparison Sorting Alphabetical list of people List of countries ordered by population

11/25/2016 Introduction to Sorting Stacks, queues, priority queues, and dictionaries all focused on providing one element at a time But often we know we want all the things in some order Humans can sort, but computers can

414 views • 6 slides
Worst-Case Ef fi cient External-Memory Priority Queues Gerth Stlting Brodal

Worst-Case Ef fi cient External-Memory Priority Queues Gerth Stlting Brodal

Worst-Case Ef fi cient External-Memory Priority Queues Gerth Stlting Brodal MaxPlanckInstitut f ur Informatik Saarbr ucken Germany Jyrki Katajainen Datalogisk Institut Kbenhavns Universitet Denmark July 1998 Worst-Case Ef

161 views • 12 slides

More recommend