Transcript External Sorting

External Sorting
Chapter 13
Database Management Systems, R. Ramakrishnan and J. Gehrke
1
Why Sort?
A classic problem in computer science!
 Data requested in sorted order

–
–
e.g., find students in increasing gpa order
Nearest neighbor search also needs sorting!
Sorting is the 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.

Database Management Systems, R. Ramakrishnan and J. Gehrke
2
Sorting Types

In-Memory Sorting: When the size of
memory is bigger than that of file to be
sorted!

External Sorting: When the size of file to be
sorted is bigger than that of available
memory!
Database Management Systems, R. Ramakrishnan and J. Gehrke
3
In-Memory Sorting: Heap-Sorting
Original
Data Page
4
Sorted
Data Page
16
1
14
3
2
10
16
9
9
8
10
7
14
4
3
8
7
2
1
How can we obtain the sorted data page?
1. Build-Heap Procedure
2. Heapsort Procedure
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
In-Memory Sorting: Heap-Sorting

Build-Heap Procedure
16
4
How?
1
16
8
10
3
8
2
14
14
9
7
9
3
10
2
4
1
7
What are the differences and the similarities
between these two heap trees?
Database Management Systems, R. Ramakrishnan and J. Gehrke
5
16
16
14
14
2
10
8
8
7
4
10
9
7
9
3
3
2
1
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
1
6
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
16
14
14
8
2
10
7
4
9
8
4
3
1
Database Management Systems, R. Ramakrishnan and J. Gehrke
2
10
7
1
9
3
16
7
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
14
10
8
4
2
10
7
1
9
8
3
16
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
2
9
7
1
3
14 16
8
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
10
9
8
4
2
9
7
1
8
3
4
3
7
1
2
14 16
10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
9
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
9
8
8
4
3
7
1
7
2
10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
3
2
1
9 10 14 16
10
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
8
7
7
4
3
2
1
9 10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
4
1
3
2
8 9 10 14 16
11
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
7
4
1
4
3
2
8 9 10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
2
3
1
7 8 9 10 14 16
12
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
4
2
3
3
2
1
1
7 8 9 10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
4 7 8 9 10 14 16
13
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
3
2
2
1
4 7 8 9 10 14 16
Database Management Systems, R. Ramakrishnan and J. Gehrke
1
3 4 7 8 9 10 14 16
14
In Memory Sorting: Heap-Sorting

Heap-Sort Procedure
Original
Data Page
4
Sorted
Data Page
16 14 10
1
3
2
16
9
9
8
10
7 4
Database Management Systems, R. Ramakrishnan and J. Gehrke
14
8
7
3 2 1
15
If file size is bigger than main memory, how can we do sorting?
Main Memory
a. Partition file into
small sub-files.
b. Sorting these sub-files
sequentially.
I/O
I/O cost: # pass *pages*2
Data Storage Disk
Database Management Systems, R. Ramakrishnan and J. Gehrke
16
2-Way Sort: Requires 3 Buffers

Pass 1: Read a page, sort it, write it.
–

only one buffer page is used ---scan over the data set
Pass 2, 3, …, etc.:
–
three buffer pages used.
I/O cost: # pass *pages*2
INPUT 1
OUTPUT
INPUT 2
Disk
Main memory buffers
Disk
Only three pages of main memory are available!
Database Management Systems, R. Ramakrishnan and J. Gehrke
17
Two-Way External Merge Sort



Each pass we read + write
each page in file.
N pages in the file => the
number of passes
  log2 N   1
So total cost is:


2 N  log 2 N   1

3,4
6,2
9,4
8,7
5,6
3,1
2
3,4
2,6
4,9
7,8
5,6
1,3
2
4,7
8,9
2,3
4,6
Input file
PASS 0
1-page runs
PASS 1
16
16
1,3
5,6
2
2-page runs
PASS 2
16
2,3
4,4
6,7
8,9
Idea: Divide and conquer:
sort subfiles and merge
Database Management Systems, R. Ramakrishnan and J. Gehrke
1,2
3,5
6
4-page runs
PASS 3
1,2
2,3
3,4
4,5
6,6
7,8
9
16
8-page runs
I/O: 64
18
General External Merge Sort
 More than 3 buffer pages. How can we utilize them?
 To sort a file with N pages using B buffer pages:
–
–
Pass 0: use B buffer pages. Produce  N / B sorted runs of B
pages each. ----each unit has B pages vs. 2 pages
Pass 2, …, etc.: merge B-1 runs. ---one page for output
INPUT 1
...
INPUT 2
...
OUTPUT
...
INPUT B-1
Disk
B Main memory buffers
Database Management Systems, R. Ramakrishnan and J. Gehrke
Disk
19
Cost of External Merge Sort
Number of passes: 1   log B 1  N / B 
 Cost = 2N * (# of passes)
 E.g., with 5 buffer pages, to sort 108 page file:
– Pass 0: 108 / 5  = 22 sorted runs of 5 pages each

–
–
–
(last run is only 3 pages)
Pass 1:  22 / 4  = 6 sorted runs of 20 pages each
(last run is only 8 pages) ---use one page for output
Pass 2: [6/4]=2 sorted runs, 80 pages and 28 pages
Pass 3: Sorted file of 108 pages
Database Management Systems, R. Ramakrishnan and J. Gehrke
20
Number of Passes of External Sort
1   log B 1  N / B 
N
B=3 B=5
100
7
4
1,000
10
5
10,000
13
7
100,000
17
9
1,000,000
20
10
10,000,000
23
12
100,000,000
26
14
1,000,000,000 30
15
B=9
3
4
5
6
7
8
9
10
Database Management Systems, R. Ramakrishnan and J. Gehrke
B=17 B=129 B=257
2
1
1
3
2
2
4
2
2
5
3
3
5
3
3
6
4
3
7
4
4
8
5
4
21
I/O for External Merge Sort
… longer runs often means fewer passes!
 Actually, do I/O a page at a time
 In fact, read a block of pages sequentially!
 Suggests we should make each buffer
(input/output) be a block of pages.

–
–
But this will reduce fan-out during merge passes!
In practice, most files still sorted in 2-3 passes.
Database Management Systems, R. Ramakrishnan and J. Gehrke
22
Number of Passes of Optimized Sort
N
100
1,000
10,000
100,000
1,000,000
10,000,000
100,000,000
1,000,000,000
B=1,000
1
1
2
3
3
4
5
5
B=5,000
1
1
2
2
2
3
3
4
B=10,000
1
1
1
2
2
3
3
3
 Block size = 32, initial pass produces runs of size 2B.
Database Management Systems, R. Ramakrishnan and J. Gehrke
23
Double Buffering

To reduce wait time for I/O request to
complete, can prefetch into `shadow block’.
–
Potentially, more passes; in practice, most files still
sorted in 2-3 passes.
INPUT 1
INPUT 1'
INPUT 2
INPUT 2'
OUTPUT
OUTPUT'
b
Disk
INPUT k
block size
Disk
INPUT k'
B main memory buffers, k-way merge
Database Management Systems, R. Ramakrishnan and J. Gehrke
24
Using B+ Trees for Sorting
Scenario: Table to be sorted has B+ tree index on
sorting column(s).
 Idea: Can retrieve records in order by traversing
leaf pages.
 Is this a good idea?
 Cases to consider:

–
–
B+ tree is clustered
B+ tree is not clustered
Good idea!
Could be a very bad idea!
Database Management Systems, R. Ramakrishnan and J. Gehrke
25
Clustered B+ Tree Used for Sorting


Cost: root to the leftmost leaf, then retrieve
all leaf pages
(Alternative 1)
If Alternative 2 is used?
Additional cost of
retrieving data records:
each page fetched just
once.
Index
(Directs search)
Data Entries
("Sequence set")
Data Records
 Always better than external sorting!
Database Management Systems, R. Ramakrishnan and J. Gehrke
26
Unclustered B+ Tree Used for Sorting

Alternative (2) for data entries; each data
entry contains rid of a data record. In general,
one I/O per data record!
Index
(Directs search)
Data Entries
("Sequence set")
Data Records
Database Management Systems, R. Ramakrishnan and J. Gehrke
27
Summary
External sorting is important; DBMS may dedicate
part of buffer pool for sorting!
 External merge sort minimizes disk I/O cost:

–
–
–
–
–
Pass 0: Produces sorted runs of size B (# buffer pages).
Later passes: merge runs.
# of runs merged at a time depends on B, and block size.
Larger block size means less I/O cost per page.
Larger block size means smaller # runs merged.
In practice, # of runs rarely more than 2 or 3.
Database Management Systems, R. Ramakrishnan and J. Gehrke
28
Summary, cont.

Choice of internal sort algorithm may matter:
–
–

The best sorts are wildly fast:
–

Quicksort: Quick!
Heap/tournament sort: slower (2x), longer runs
Despite 40+ years of research, we’re still
improving!
Clustered B+ tree is good for sorting;
unclustered tree is usually very bad.
Database Management Systems, R. Ramakrishnan and J. Gehrke
29