Sorting & Joins Database Systems Andy Pavlo Lecture #11 - - PowerPoint PPT Presentation
Sorting & Joins Database Systems Andy Pavlo Lecture #11 15-445/15-645 Computer Science Dept. Fall 2017 Carnegie Mellon Univ. ADMINISTRIVIA Homework #3 is due TODAY @ 11:59pm Homework #4 is due Wednesday October 11 th @ 11:59pm CMU
Database Systems 15-445/15-645 Fall 2017 Andy Pavlo Computer Science Dept. Carnegie Mellon Univ. Lecture #11
CMU 15-445/645 (Fall 2017)
ADMINISTRIVIA
Homework #3 is due TODAY @ 11:59pm Homework #4 is due Wednesday October 11th @ 11:59pm
2
CMU 15-445/645 (Fall 2017)
STATUS
We will continue our discussion
queries. We will focus on a couple of frequently used relational
3
Query Planning Operator Execution Access Methods Buffer Pool Manager Disk Manager
CMU 15-445/645 (Fall 2017)
TODAY'S AGENDA
Sorting algorithms Join algorithms
4
CMU 15-445/645 (Fall 2017)
WHY DO WE NEED TO SORT?
Relational model
→ Tuples in a table have no specific order
SELECT...ORDER BY
→ Users often want to retrieve tuples in a specific order → Trivial to support duplicate elimination (DISTINCT) → Bulk loading sorted tuples into a B+ tree index is faster
SELECT...GROUP BY
→ Sort-merge join algorithm
5
CMU 15-445/645 (Fall 2017)
SORTING ALGORITHMS
Data fits in memory: Then we can use a standard sorting algorithm like quick-sort. Data does not fit in memory: Sorting data that does not fit in main-memory is called external sorting.
6
CMU 15-445/645 (Fall 2017)
EXTERNAL MERGE SORT
A frequently used external sorting algorithm. Idea: Hybrid sort-merge strategy
→ Sorting phase: Sort small chunks of data that fit in main-memory, and then write back the sorted data to a file on disk. → Merge phase: Combine sorted sub-files into a single larger file.
7
CMU 15-445/645 (Fall 2017)
OVERVIEW
Let’s start with a simple example: 2-way external merge sort. Later generalize it to k-way external merge sort. Files are broken up into N pages. The DBMS has a finite number of B fixed-size buffers.
8
CMU 15-445/645 (Fall 2017)
2-WAY EXTERNAL MERGE SORT
Pass 0:
→ Reads every B pages of the table into memory → Sorts them, and writes them back to disk. → Each sorted set of pages is a run
Pass 1,2,3,…:
→ Recursively merges pairs of runs into runs twice as long → Uses three buffer pages (two for input pages, one for output)
9 Memory Memory Memory Disk
CMU 15-445/645 (Fall 2017)
2-WAY EXTERNAL MERGE SORT
In each pass, we read and write each page in file. Number of passes = 1 + ⌈ log2 N ⌉ Total I/O cost = 2N · (# of passes) Divide and conquer strategy: Sort sub-files and merge
INPUT FILE 1-PAGE RUNS 2-PAGE RUNS 4-PAGE RUNS 8-PAGE RUNS PASS #0 PASS #1 PASS #2 PASS #3
3 , 4 2 , 6 4 , 9 7 , 8 5 , 6 1 , 3 2 2 , 3 4 , 6 4 , 7 8 , 9 1 , 3 5 , 6 2 1 , 2 3 , 5 6 1 , 2 2 , 3 3 , 4 4 , 5 6 , 6 7 , 8 9 6 , 2 9 , 4 8 , 7 5 , 6 3 , 1 2 3 , 4 4 , 4 6 , 7 8 , 9 2 , 3
NULL
10
CMU 15-445/645 (Fall 2017)
2-WAY EXTERNAL MERGE SORT
This algorithm only requires three buffer pages (B=3). Even if we have more buffer space available (B>3), it does not effectively utilize them. Let’s next generalize the algorithm to make use of extra buffer space.
11
CMU 15-445/645 (Fall 2017)
GENERAL EXTERNAL MERGE SORT
Pass 0: Use B buffer pages. Produce ⌈N / B⌉ sorted runs of size B Pass 1,2,3,…: Merge B-1 runs. (K-way merge) Number of passes = 1 + ⌈ logB-1⌈N / B⌉ ⌉ Total I/O Cost = 2N·(# of passes)
12
CMU 15-445/645 (Fall 2017)
K-WAY MERGE ALGORITHM
Input: K sorted sub-arrays Efficiently computes the minimum element of all K sub-arrays Repeatedly transfers that element to
Internally maintains a heap to efficiently compute minimum element Time Complexity = O(N log2K)
13
CMU 15-445/645 (Fall 2017)
EXAMPLE
Sort 108 page file with 5 buffer pages: N=108, B=5
→ Pass 0: ⌈N / B⌉ = ⌈108 / 5⌉ = 22 sorted runs of 5 pages each (last run is only 3 pages) → Pass 1: ⌈N’ / B-1⌉ = ⌈22 / 4⌉ = 6 sorted runs of 20 pages each (last run is only 8 pages) → Pass 2: ⌈N’’ / B-1⌉ = ⌈6 / 4⌉ = 2 sorted runs, 80 pages and 28 pages → Pass 3: Sorted file of 108 pages
1+⌈ logB-1⌈N / B⌉ ⌉ = 1+⌈log4 22⌉ = 1+⌈2.229...⌉ à 4 passes
14
CMU 15-445/645 (Fall 2017)
USING B+TREES
Scenario: Table that must be sorted already has a B+ tree index on the sort attribute(s). Can we accelerate sorting? Idea: Retrieve tuples in desired sort order by simply traversing the leaf pages of the tree. Cases to consider:
→ Clustered B+ tree → Unclustered B+ tree
15
CMU 15-445/645 (Fall 2017)
CASE 1: CLUSTERED B+TREE
(Directs search) Data Records Index Data Entries ("Sequence set")
Traverse to the left-most leaf page, and then retrieve tuples from all leaf pages. Always better than external
16
101 102 103 104
CMU 15-445/645 (Fall 2017)
CASE 2: UNCLUSTERED B+TREE
Chase each pointer to the page that contains the data. In general, one I/O per data
17
101 102 103 104
(Directs search) Data Records Index Data Entries ("Sequence set")
CMU 15-445/645 (Fall 2017)
ALTERNATIVES TO SORTING
What if we don’t need the data to be ordered?
→ Forming groups in GROUPBY (no ordering) → Removing duplicates in DISTINCT (no ordering)
Can we remove duplicates without sorting?
→ Hashing is a better alternative in this scenario → Only need to remove duplicates, no need for ordering → Can be computationally cheaper than sorting!
18
CMU 15-445/645 (Fall 2017)
SORTING: SUMMARY
External merge sort minimizes disk I/O
→ Pass 0: Produces sorted runs of size B → Later Passes: Recursively merge runs
Next week: Query optimizer picks a sorting or hashing operator based on
19
CMU 15-445/645 (Fall 2017)
TODAY'S AGENDA
Sorting algorithms Join algorithms
20
CMU 15-445/645 (Fall 2017)
WHY DO WE NEED TO JOIN?
Relational model
→ Unnecessary repetition of information must be avoided → We decompose tables using normalization theory
SELECT...JOIN
→ Reconstruct original tables via joins → No information loss
21
CMU 15-445/645 (Fall 2017) 22
Anybody here into sailing?
CMU 15-445/645 (Fall 2017)
SID BID DAY RNAME 6 103 2014-02-01 Matlock 1 102 2014-02-02 Macgyver 2 101 2014-02-02 A-team 1 101 2014-02-01 Dallas
SAILING CLUB DATABASE
SAILORS RESERVES
SID SNAME RATING AGE 1 Andy 999 45.0 3 Obama 50 52.0 2 Tupac 32 26.0 6 Bieber 10 19.0
Sailors(sid: int, sname: varchar, rating: int, age: real) Reserves(sid: int, bid: int, day: date, rname: varchar)
23
CMU 15-445/645 (Fall 2017)
SAILING CLUB DATABASE
Each tuple is 50 bytes 80 tuples per page 500 pages total N=500, pS=80 Each tuple is 40 bytes 100 tuples per page 1000 pages total M=1000, pR=100
SID BID DAY RNAME 6 103 2014-02-01 Matlock 1 102 2014-02-02 Macgyver 2 101 2014-02-02 A-team 1 101 2014-02-01 Dallas
SAILORS RESERVES
SID SNAME RATING AGE 1 Andy 999 45.0 3 Obama 50 52.0 2 Tupac 32 26.0 6 Bieber 10 19.0
24
CMU 15-445/645 (Fall 2017)
JOIN VS CROSS-PRODUCT
R⨝S is very common and thus must be carefully optimized. R×S followed by a selection is inefficient because the cross-product is large. There are many algorithms for reducing join cost, but no particular algorithm works well in all scenarios.
25
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS
Join algorithms we will cover in today’s lecture:
→ Simple Nested Loop Join → Block Nested Loop Join → Index Nested Loop Join → Sort-Merge Join → Hash Join (next lecture)
26
CMU 15-445/645 (Fall 2017)
I/O COST ANALYSIS
Assume:
→ M pages in R, pR tuples per page, m tuples total → N pages in S, pS tuples per page, n tuples total → In our examples, R is Reserves and S is Sailors.
We will consider more complex join conditions later. Cost metric: # of I/Os
We will ignore
27
CMU 15-445/645 (Fall 2017)
JOIN QUERY EXAMPLE
Assume that we don’t know anything about the tables and we don’t have any indexes.
SELECT * FROM Reserves R, Sailors S WHERE R.sid = S.sid
28
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS
Join algorithms we will cover:
→ Simple Nested Loop Join → Block Nested Loop Join → Index Nested Loop Join → Sort-Merge Join
29
CMU 15-445/645 (Fall 2017)
SIMPLE NESTED LOOP JOIN
R(A,..) S(A, ......)
foreach tuple r of R foreach tuple s of S
30
CMU 15-445/645 (Fall 2017)
foreach tuple r of R foreach tuple s of S
SIMPLE NESTED LOOP JOIN
Outer table Inner table R(A,..) S(A, ......)
31
CMU 15-445/645 (Fall 2017)
SIMPLE NESTED LOOP JOIN
Why is this algorithm bad?
→ For every tuple in R, it scans S once
Number of disk accesses
→ Cost: M + (m · N)
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
32
CMU 15-445/645 (Fall 2017)
Actual number:
→ M + (m · N) = 1000 + (100 · 1000) · 500 ≈ 50 M I/Os → At 0.1 ms/IO, Total time ≈ 1.3 hours
What if smaller table (S) is used as the outer table?
→ N + (n · M) = 500 + (80 ·500) · 1000 ≈ 40 M I/Os → Slightly better.
What assumptions are being made here?
→ 2 buffers for streaming the tables (and 1 for storing output)
SIMPLE NESTED LOOP JOIN
33
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS
Join algorithms we will cover:
→ Simple Nested Loop Join → Block Nested Loop Join → Index Nested Loop Join → Sort-Merge Join
34
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
read block from R read block from S
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
35
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
This algorithm performs fewer disk accesses.
→ For every block in R, it scans S once
Number of disk accesses
→ Cost: M + (M·N)
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
36
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
Algorithm Optimizations: Which one should be the outer table?
→ The smaller table (in terms of # of pages)
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
37
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
Actual number:
→ M + (M·N) = 1000 + (1000 · 500) ≈ 0.5 M I/Os → At 0.1 ms/IO, Total time ≈ 50 seconds
What if we have B buffers available?
→ Use B-2 buffers for scanning outer table, → Use 1 buffer to scanning inner table, 1 buffer for storing output
38
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
read B-2 blocks from R read block from S
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
39
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
This algorithm uses B-2 buffers for scanning M. Number of disk accesses
→ Cost: M + ( éM/(B-2)ù ·N)
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
40
CMU 15-445/645 (Fall 2017)
BLOCK NESTED LOOP JOIN
What if the outer relation completely fits in memory (B>M+2)?
→ Cost: M + N = 1000 + 500 = 1500 I/Os → At 0.1ms/IO, Total time ≈ 0.15 seconds
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
41
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS
Join algorithms we will cover:
→ Simple Nested Loop Join → Block Nested Loop Join → Index Nested Loop Join → Sort-Merge Join
42
CMU 15-445/645 (Fall 2017)
INDEX NESTED LOOP JOIN
Why do basic nested loop joins suck?
→ For each tuple in the outer table, we have to do a sequential scan to check for a match in the inner table.
Can we accelerate the join using an index? Use an index to find inner table matches.
→ We could use an existing index for the join. → Or even build one on the fly.
43
CMU 15-445/645 (Fall 2017)
INDEX NESTED LOOP JOIN
foreach tuple r of R foreach tuple s of S, where ri = sj
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
Index Probe
44
CMU 15-445/645 (Fall 2017)
INDEX NESTED LOOP JOIN
Number of disk accesses
→ Cost: M + ( m · C )
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
Index Look-up Cost
45
CMU 15-445/645 (Fall 2017)
NESTED LOOP JOIN: SUMMARY
Pick the smaller table as the outer table. Buffer as much of the outer table in memory as possible. Loop over the inner table or use an index.
46
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS
Join algorithms we will cover:
→ Simple Nested Loop Join → Block Nested Loop Join → Index Nested Loop Join → Sort-Merge Join
47
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN
Sort Phase: First sort both tables on the join attribute. Merge Phase: Then scan the two sorted tables in parallel, and emit matching tuples.
48
CMU 15-445/645 (Fall 2017)
WHEN IS SORT-MERGE JOIN USEFUL?
This join algorithm is useful if:
→ One or both tables are already sorted on join key → Output must be sorted on join key
Sorting: Might be achieved either by an explicit sort step, or by scanning the relation using an index on the join key.
49
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN EXAMPLE
Sort! Sort!
SELECT * FROM Reserves R, Sailors S WHERE R.sid = S.sid
SID BID DAY RNAME 6 103 2014-02-01 Matlock 1 102 2014-02-02 Macgyver 2 101 2014-02-02 A-team 1 101 2014-02-01 Dallas SID SNAME RATING AGE 1 Andy 999 45.0 3 Obama 50 52.0 2 Tupac 32 26.0 6 Bieber 10 19.0 SID BID DAY RNAME 1 102 2014-02-02 Macgyver 1 101 2014-02-01 Dallas 2 101 2014-02-02 A-team 6 103 2014-02-01 Matlock SID SNAME RATING AGE 1 Andy 999 45.0 2 Tupac 32 26.0 3 Obama 50 52.0 6 Bieber 10 19.0
50
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN EXAMPLE
SELECT * FROM Reserves R, Sailors S WHERE R.sid = S.sid
SID BID DAY RNAME 1 102 2014-02-02 Macgyver 1 101 2014-02-01 Dallas 2 101 2014-02-02 A-team 6 103 2014-02-01 Matlock SID SNAME RATING AGE 1 Andy 999 45.0 2 Tupac 32 26.0 3 Obama 50 52.0 6 Bieber 10 19.0
Merge! Merge!
✔ ✔ ✔ ✔
51
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN
Number of disk accesses
→ Cost: [ (2M · logM/logB) + (2N · logN/logB) ] + [ M + N ]
M pages, m tuples N pages, n tuples
R(A,..) S(A, ......)
Sort Cost Merge Cost
52
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN
With 100 buffer pages, both R and S can be sorted in 2 passes:
→ Cost: 7,500 I/Os → At 0.1 ms/IO, Total time ≈ 0.75 seconds
53
CMU 15-445/645 (Fall 2017)
SORT-MERGE JOIN
Worst case for merging phase?
→ When the join attribute of all of the tuples in both relations contain the same value. → Cost: (M · N) + (sort cost)
Andy: Don’t worry kids! This is unlikely!
54
CMU 15-445/645 (Fall 2017)
JOIN ALGORITHMS: SUMMARY
JOIN ALGORITHM I/O COST TOTAL TIME Simple Nested Loop Join M + (m·N) 1.3 hours Block Nested Loop Join M + (M·N) 50 seconds Index Nested Loop Join M + (m·log N) 20 seconds Sort Merge Join M + N + (sort cost) 0.75 seconds
55
CMU 15-445/645 (Fall 2017)
JOIN TYPES
LEFT OUTER JOIN LEFT OUTER JOIN WITH EXCLUSION INNER JOIN FULL OUTER JOIN RIGHT OUTER JOIN RIGHT OUTER JOIN WITH EXCLUSION FULL OUTER JOIN WITH EXCLUSION
56
CMU 15-445/645 (Fall 2017)
CASE STUDY: POSTGRESQL
Employs a state machine to track the join algorithm’s state
→ At each state, does something and then proceeds to another state → State transitions depend on join type
JOIN ALGORITHM STATES
57
CMU 15-445/645 (Fall 2017)
CONCLUSION
There are many join algorithms.
→ Illustrates the sophistication of the technology underlying database systems.
Picking a join algorithm is challenging.
→ Index Nested Loop when selectivity is small. → Sort-Merge when joining whole tables.
Stay tuned for more details in next week’s query optimization lecture.
58
CMU 15-445/645 (Fall 2017)
RECAP
Sorting algorithms Join algorithms
59
CMU 15-445/645 (Fall 2017)
NEXT CLASS
Join Algorithms: Hash Join More Exotic Join Types: Semi, Anti, Lateral Aggregation Algorithms
60