cs 277 – spring 2002notes 51 cs 277: database system implementation arthur keller notes 5: hashing...
Post on 21-Dec-2015
217 views
TRANSCRIPT
![Page 1: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/1.jpg)
CS 277 – Spring 2002
Notes 5 1
CS 277: Database System Implementation
Arthur Keller
Notes 5: Hashing and More
![Page 2: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/2.jpg)
CS 277 – Spring 2002
Notes 5 2
key h(key)
Hashing
<key>
.
.
Buckets(typically 1disk block)
![Page 3: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/3.jpg)
CS 277 – Spring 2002
Notes 5 3
.
.
.
Two alternatives
records
.
.
.
(1) key h(key)
![Page 4: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/4.jpg)
CS 277 – Spring 2002
Notes 5 4
(2) key h(key)
Index
recordkey 1
Two alternatives
• Alt (2) for “secondary” search key
![Page 5: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/5.jpg)
CS 277 – Spring 2002
Notes 5 5
Example hash function
• Key = ‘x1 x2 … xn’ n byte character string
• Have b buckets• h: add x1 + x2 + ….. xn
– compute sum modulo b
![Page 6: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/6.jpg)
CS 277 – Spring 2002
Notes 5 6
This may not be best function … Read Knuth Vol. 3 if you really
need to select a good function.
Good hash Expected number of function: keys/bucket is the
same for all buckets
![Page 7: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/7.jpg)
CS 277 – Spring 2002
Notes 5 7
Within a bucket:
• Do we keep keys sorted?
• Yes, if CPU time critical & Inserts/Deletes not too frequent
![Page 8: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/8.jpg)
CS 277 – Spring 2002
Notes 5 8
Next: example to illustrateinserts, overflows,
deletes
h(K)
![Page 9: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/9.jpg)
CS 277 – Spring 2002
Notes 5 9
EXAMPLE 2 records/bucket
INSERT:h(a) = 1h(b) = 2h(c) = 1h(d) = 0
0
1
2
3
d
ac
b
h(e) = 1
e
![Page 10: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/10.jpg)
CS 277 – Spring 2002
Notes 5 10
0
1
2
3
a
bc
e
d
EXAMPLE: deletion
Delete:ef
fg
maybe move“g” up
cd
![Page 11: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/11.jpg)
CS 277 – Spring 2002
Notes 5 11
Rule of thumb:• Try to keep space utilization
between 50% and 80% Utilization = # keys used
total # keys that fit
• If < 50%, wasting space• If > 80%, overflows significant
depends on how good hashfunction is & on # keys/bucket
![Page 12: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/12.jpg)
CS 277 – Spring 2002
Notes 5 12
How do we cope with growth?
• Overflows and reorganizations• Dynamic hashing
• Extensible• Linear
![Page 13: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/13.jpg)
CS 277 – Spring 2002
Notes 5 13
Extensible hashing: two ideas
(a) Use i of b bits output by hash function
b h(K)
use i grows over time….
00110101
![Page 14: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/14.jpg)
CS 277 – Spring 2002
Notes 5 14
(b) Use directory
h(K)[i ] to bucket
.
.
.
.
![Page 15: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/15.jpg)
CS 277 – Spring 2002
Notes 5 15
Example: h(k) is 4 bits; 2 keys/bucket
i = 1
1
1
0001
1001
1100
Insert 1010
11100
1010
New directory
200
01
10
11
i =
2
2
![Page 16: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/16.jpg)
CS 277 – Spring 2002
Notes 5 16
10001
21001
1010
21100
Insert:
0111
0000
00
01
10
11
2i =
Example continued
0111
0000
0111
0001
2
2
![Page 17: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/17.jpg)
CS 277 – Spring 2002
Notes 5 17
00
01
10
11
2i =
21001
1010
21100
20111
20000
0001
Insert:
1001
Example continued
1001
1001
1010
000
001
010
011
100
101
110
111
3i =
3
3
![Page 18: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/18.jpg)
CS 277 – Spring 2002
Notes 5 18
Extensible hashing: deletion
• No merging of blocks• Merge blocks
and cut directory if possible(Reverse insert procedure)
![Page 19: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/19.jpg)
CS 277 – Spring 2002
Notes 5 19
Deletion example:
• Run thru insert example in reverse!
![Page 20: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/20.jpg)
CS 277 – Spring 2002
Notes 5 20
Extensible hashing
Can handle growing files- with less wasted space- with no full reorganizations
Summary
+
Indirection(Not bad if directory in
memory)
Directory doubles in size(Now it fits, now it does not)
-
-
![Page 21: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/21.jpg)
CS 277 – Spring 2002
Notes 5 21
Linear hashing
• Another dynamic hashing scheme
Two ideas:(a) Use i low order bits of hash
01110101grows
b
i
(b) File grows linearly
![Page 22: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/22.jpg)
CS 277 – Spring 2002
Notes 5 22
Example b=4 bits, i =2, 2 keys/bucket
00 01 10 11
0101
1111
0000
1010
m = 01 (max used block)
Futuregrowthbuckets
If h(k)[i ] m, then look at bucket h(k)[i ]
else, look at bucket h(k)[i ] - 2i -1
Rule
0101• can have overflow chains!
• insert 0101
![Page 23: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/23.jpg)
CS 277 – Spring 2002
Notes 5 23
Example b=4 bits, i =2, 2 keys/bucket
00 01 10 11
0101
1111
0000
1010
m = 01 (max used block)
Futuregrowthbuckets
10
1010
0101 • insert 0101
11
11110101
![Page 24: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/24.jpg)
CS 277 – Spring 2002
Notes 5 24
Example Continued: How to grow beyond this?
00 01 10 11
111110100101
0101
0000
m = 11 (max used block)
i = 2
0 0 0 0100 101 110 111
3
. . .
100
100
101
101
0101
0101
![Page 25: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/25.jpg)
CS 277 – Spring 2002
Notes 5 25
• If U > threshold then increase m(and maybe i )
When do we expand file?
• Keep track of: # used slots total # of slots = U
![Page 26: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/26.jpg)
CS 277 – Spring 2002
Notes 5 26
Linear Hashing
Can handle growing files- with less wasted space- with no full reorganizations
No indirection like extensible hashing
Summary
+
+
Can still have overflow chains-
![Page 27: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/27.jpg)
CS 277 – Spring 2002
Notes 5 27
Example: BAD CASE
Very full
Very empty Need to move
m here…Would wastespace...
![Page 28: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/28.jpg)
CS 277 – Spring 2002
Notes 5 28
Hashing- How it works- Dynamic hashing
- Extensible- Linear
Summary
![Page 29: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/29.jpg)
CS 277 – Spring 2002
Notes 5 29
Next:
• Indexing vs Hashing• Index definition in SQL• Multiple key access
![Page 30: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/30.jpg)
CS 277 – Spring 2002
Notes 5 30
• Hashing good for probes given keye.g., SELECT …
FROM RWHERE R.A = 5
Indexing vs Hashing
![Page 31: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/31.jpg)
CS 277 – Spring 2002
Notes 5 31
• INDEXING (Including B Trees) good for
Range Searches:e.g., SELECT
FROM RWHERE R.A > 5
Indexing vs Hashing
![Page 32: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/32.jpg)
CS 277 – Spring 2002
Notes 5 32
Index definition in SQL
• Create index name on rel (attr)• Create unique index name on rel
(attr)defines candidate key
• Drop INDEX name
![Page 33: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/33.jpg)
CS 277 – Spring 2002
Notes 5 33
CANNOT SPECIFY TYPE OF INDEX
(e.g. B-tree, Hashing, …)
OR PARAMETERS(e.g. Load Factor, Size of
Hash,...)
... at least in SQL...
Note
![Page 34: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/34.jpg)
CS 277 – Spring 2002
Notes 5 34
ATTRIBUTE LIST MULTIKEY INDEX
(next) e.g., CREATE INDEX foo ON
R(A,B,C)
Note
![Page 35: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/35.jpg)
CS 277 – Spring 2002
Notes 5 35
Motivation: Find records where DEPT = “Toy” AND SAL >
50k
Multi-key Index
![Page 36: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/36.jpg)
CS 277 – Spring 2002
Notes 5 36
Strategy I:
• Use one index, say Dept.• Get all Dept = “Toy” records
and check their salary
I1
![Page 37: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/37.jpg)
CS 277 – Spring 2002
Notes 5 37
• Use 2 Indexes; Manipulate Pointers
Toy Sal>
50k
Strategy II:
![Page 38: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/38.jpg)
CS 277 – Spring 2002
Notes 5 38
• Multiple Key Index
One idea:
Strategy III:
I1
I2
I3
![Page 39: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/39.jpg)
CS 277 – Spring 2002
Notes 5 39
Example
ExampleRecord
DeptIndex
SalaryIndex
Name=JoeDEPT=SalesSAL=15k
ArtSalesToy
10k15k17k21k
12k15k15k19k
![Page 40: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/40.jpg)
CS 277 – Spring 2002
Notes 5 40
For which queries is this index good?
Find RECs Dept = “Sales” SAL=20kFind RECs Dept = “Sales” SAL > 20kFind RECs Dept = “Sales”Find RECs SAL = 20k
![Page 41: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/41.jpg)
CS 277 – Spring 2002
Notes 5 41
Interesting application:
• Geographic Data
DATA:
<X1,Y1, Attributes> <X2,Y2, Attributes>
x
y
. .
.
![Page 42: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/42.jpg)
CS 277 – Spring 2002
Notes 5 42
Queries:
• What city is at <Xi,Yi>?• What is within 5 miles from
<Xi,Yi>?• Which is closest point to <Xi,Yi>?
![Page 43: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/43.jpg)
CS 277 – Spring 2002
Notes 5 43
h
nb
ia
co
d
10 20
10 20
Examplee
g
f
m
l
kj25 15 35 20
40
30
20
10
h i a bcd efg
n omlj k
• Search points near f• Search points near b
5
15 15
![Page 44: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/44.jpg)
CS 277 – Spring 2002
Notes 5 44
Queries
• Find points with Yi > 20• Find points with Xi < 5• Find points “close” to i = <12,38>• Find points “close” to b = <7,24>
![Page 45: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/45.jpg)
CS 277 – Spring 2002
Notes 5 45
• Many types of geographic index structures have been suggested
• Quad Trees• R Trees
![Page 46: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/46.jpg)
CS 277 – Spring 2002
Notes 5 46
Two more types of multi key indexes
• Grid• Partitioned hash
![Page 47: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/47.jpg)
CS 277 – Spring 2002
Notes 5 47
Grid Index Key 2
X1 X2 …… Xn V1 V2
Key 1
Vn
To records with key1=V3, key2=X2
![Page 48: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/48.jpg)
CS 277 – Spring 2002
Notes 5 48
CLAIM
• Can quickly find records with– key 1 = Vi Key 2 = Xj
– key 1 = Vi
– key 2 = Xj
• And also ranges….– E.g., key 1 Vi key 2 < Xj
![Page 49: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/49.jpg)
CS 277 – Spring 2002
Notes 5 49
But there is a catch with Grid Indexes!
• How is Grid Index stored on disk?
LikeArray... X
1X
2X
3X
4
X1
X2
X3
X4
X1
X2
X3
X4
V1 V2 V3
Problem:• Need regularity so we can compute
position of <Vi,Xj> entry
![Page 50: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/50.jpg)
CS 277 – Spring 2002
Notes 5 50
Solution: Use Indirection
BucketsV1V2
V3 *Grid onlyV4 contains
pointers tobuckets
Buckets------
------
------
------
------
X1 X2 X3
![Page 51: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/51.jpg)
CS 277 – Spring 2002
Notes 5 51
With indirection:
• Grid can be regular without wasting space
• We do have price of indirection
![Page 52: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/52.jpg)
CS 277 – Spring 2002
Notes 5 52
Can also index grid on value ranges
Salary Grid
Linear Scale1 2 3
Toy SalesPersonnel
0-20K 1
20K-50K 2
50K- 38
![Page 53: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/53.jpg)
CS 277 – Spring 2002
Notes 5 53
Grid files
Good for multiple-key searchSpace, management overhead (nothing is free)
Need partitioning ranges that evenly split keys
+
-
-
![Page 54: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/54.jpg)
CS 277 – Spring 2002
Notes 5 54
Idea:
Key1 Key2
Partitioned hash function
h1 h2
010110 1110010
![Page 55: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/55.jpg)
CS 277 – Spring 2002
Notes 5 55
h1(toy) =0 000h1(sales) =1 001h1(art)=1 010
. 011
.h2(10k) =01 100h2(20k) =11 101h2(30k) =01 110h2(40k) =00 111
.
.
<Fred,toy,10k>,<Joe,sales,10k><Sally,art,30k>
EX:
Insert
<Joe><Sally>
<Fred>
![Page 56: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/56.jpg)
CS 277 – Spring 2002
Notes 5 56
h1(toy) =0 000h1(sales) =1 001h1(art)=1 010
. 011
.h2(10k) =01 100h2(20k) =11 101h2(30k) =01 110h2(40k) =00 111
.
.• Find Emp. with Dept. = Sales
Sal=40k
<Fred><Joe><Jan>
<Mary>
<Sally>
<Tom><Bill><Andy>
![Page 57: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/57.jpg)
CS 277 – Spring 2002
Notes 5 57
h1(toy) =0 000h1(sales) =1 001h1(art)=1 010
. 011
.h2(10k) =01 100h2(20k) =11 101h2(30k) =01 110h2(40k) =00 111
.
.• Find Emp. with Sal=30k
<Fred><Joe><Jan>
<Mary>
<Sally>
<Tom><Bill><Andy>
look here
![Page 58: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/58.jpg)
CS 277 – Spring 2002
Notes 5 58
h1(toy) =0 000h1(sales) =1 001h1(art)=1 010
. 011
.h2(10k) =01 100h2(20k) =11 101h2(30k) =01 110h2(40k) =00 111
.
.• Find Emp. with Dept. = Sales
<Fred><Joe><Jan>
<Mary>
<Sally>
<Tom><Bill><Andy>
look here
![Page 59: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/59.jpg)
CS 277 – Spring 2002
Notes 5 59
Post hashing discussion:- Indexing vs. Hashing
- SQL Index Definition- Multiple Key Access
- Multi Key IndexVariations: Grid, Geo Data
- Partitioned Hash
Summary
![Page 60: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/60.jpg)
CS 277 – Spring 2002
Notes 5 60
Reading Chapter 14
• Skim the following sections:– 14.3.6, 14.3.7, 14.3.8– 14.4.2, 14.4.3, 14.4.4
• Read the rest
![Page 61: CS 277 – Spring 2002Notes 51 CS 277: Database System Implementation Arthur Keller Notes 5: Hashing and More](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649d635503460f94a4617c/html5/thumbnails/61.jpg)
CS 277 – Spring 2002
Notes 5 61
The BIG picture….
• Chapters 11 & 12: Storage, records, blocks...
• Chapter 13 & 14: Access Mechanisms- Indexes
- B trees- Hashing- Multi key
• Chapter 15 & 16: Query ProcessingNEXT