![Page 1: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/1.jpg)
Database Cracking
David Werner
January 23, 2018
Technische Universitat Munchen
![Page 2: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/2.jpg)
Table Of Contents
Database cracking
Basics
Cracking index
Algorithms
Advantages
Implementation
Cracking Algorihtms
Cracking index struct
B+ − Tree
Evaluation
1
![Page 3: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/3.jpg)
Database cracking
![Page 4: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/4.jpg)
What is database cracking? - 1
• self organized indexing and index maintenance
• queries are used as advice to crack the database in pieces
• cracking means physically reordering the database
• sequential access for range queries is guaranteed
2
![Page 5: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/5.jpg)
What is database cracking? - 2
• original column stays in insertion order
• cracking column is used for reordering
• this allows fast reconstruction of records
3
![Page 6: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/6.jpg)
Cracking example
4
![Page 7: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/7.jpg)
Cracking index
• index on cracking column
• stores information about every crack
• bound value
• end position of piece
• inclusive flag
5
![Page 8: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/8.jpg)
Cracking in two pieces - basic
Algorithm 1 Crack in two pieces
1: procedure crack in 2(column, left, right, value, inclusive)
2: while left < right do
3: if column[left] ∆1 value then
4: left ← left + 1
5: else
6: while column[right] ∆2 value and left < right do
7: right ← right − 1
8: end while
9: swap(column[left], column[right])
10: left ← left + 1
11: right ← right − 1
12: end if
13: end while
14: end procedure
∆1 is < or ≤, ∆2 is > or ≥ depedending on the inclusive flag
6
![Page 9: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/9.jpg)
Cracking in two pieces - branch free
Algorithm 2 Crack in two pieces (branch free)
1: procedure crack in 2 bf(column, left, right, value, inclusive)
2: cmp
3: active ← column[left]
4: backup ← column[right]
5: while left < right do
6: cmp ← active ∆1 value
7: column[left]← active
8: column[right]← active
9: left ← left + cmp
10: right ← right − (1− cmp)
11: active ← (column[left] ∗ cmp) + (column[right] ∗ (1− cmp))
12: swap(active, backup)
13: end while
14: column[left]← active
15: end procedure
7
![Page 10: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/10.jpg)
Cracking in three pieces
Algorithm 3 Crack in three pieces
1: procedure crack in 3(column, left, right, value1, value2, inclusive1, inclusive2)
2: tmp ← left
3: while left < right do
4: while left < right and column[left] ∆1 value2 do
5: if column[left] ∆1 value1 then
6: swap(column[left], column[tmp])
7: tmp ← tmp + 1
8: end if
9: left ← left + 1
10: end while
11: while left < right and column[right] ∆2 value2 do
12: right ← right − 1
13: end while
14: if left < right then
15: swap(column[left], column[right])
16: end if
17: end while
18: end procedure
8
![Page 11: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/11.jpg)
Advantages
Database cracking has some interesting properties:
• no copying of query results
• no updfront knowledge about workload required
• physcial reordering can be supported by index
• consecutive cracks receive speed from index
9
![Page 12: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/12.jpg)
Implementation
![Page 13: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/13.jpg)
Cracking Algorihtms
• All three cracking algorithms
• Return last position of piece in cracking column
• < and ≤ cracks only
• > and ≥ queries can use these results
10
![Page 14: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/14.jpg)
Cracking index struct
• Combines cracking algorithms with cracking index
• Comprises:
• Pointer to original column
• Pointer to cracking column
• Column size
• Map as index
• Main functionality:
• Find pieces
• Query (single bound, double bound)
11
![Page 15: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/15.jpg)
Find piece - 1
exact match:
returns: true
12
![Page 16: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/16.jpg)
Find piece - 2
no match at all1 or inclusive flag does not match2 :
returns: false
13
![Page 17: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/17.jpg)
Query
Two different types of queries
• single bound (e.g. X < a)
• double bound (e.g. a < X < b)
Query method interface:
• Require bound value(s) and inclusive flag(s)
• Return start/end position of result piece(s)
14
![Page 18: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/18.jpg)
Query - single bound
simple control flow:
1. Find piece for value
2. If exact match: return
3. Otherwise: crack
4. Add crack to index
5. Return
15
![Page 19: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/19.jpg)
Query - double bound
• Find piece for both bounds
• Depending on results different cases need to be handled
• Four easy cases:
• None of both bounds needs a crack
• Both bounds need crack in different pieces
• Upper/lower bound needs crack
• Two involved cases
16
![Page 20: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/20.jpg)
Special case 1
example query: 9 ≤ X < 12
solution: crack in three pieces
17
![Page 21: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/21.jpg)
Special case 2
example query: 4 < X ≤ 13
solution: crack yellow first, use result to crack red
18
![Page 22: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/22.jpg)
Extensions and Usage
• Extensions:
• Leaves have sibling pointers
• Pointer to leftmost leaf
• Tree stores:
• bound values as keys
• position and inclusive flag as payload
19
![Page 23: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/23.jpg)
Query operation
1. Find start position
2. Find end position
3. Traverse leaves
4. Lookup column positions
5. Copy column values to output
6. Stop at end position
20
![Page 24: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/24.jpg)
Evaluation
![Page 25: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/25.jpg)
Test cases
• Comparison of”Crack in two“ algorithms
• 500’000’000
• single crack
• Cracking vs. Indexing
• 50’000’000 values in column
• 100 consecutive cracks
21
![Page 26: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/26.jpg)
Comparison of cracking algorithms
small result piece:
780.58
1220
0
250
500
750
1000
1250
basic bf
approach
timem
s
big result piece:
1888.2
1189.89
0
500
1000
1500
basic bf
approach
timem
s
22
![Page 27: Database Cracking - Technische Universität München · Database Cracking David Werner January 23, 2018 Technische Universit at M unchen. Table Of Contents Database cracking Basics](https://reader034.vdocuments.us/reader034/viewer/2022042212/5eb5063cdd3c16158e337232/html5/thumbnails/27.jpg)
Cracking vs Indexing
single crack workload:
188.59
30578.28
0
10000
20000
30000
crack index
name
timem
s
only cracks workload:
1162.91
31119.58
0
10000
20000
30000
crack index
name
timem
s
23