Evaluation of Relational Operators

1
????? ?? ????????? ?????????

• Evaluation of Relational Operators

2
????? ???? ?? ???????

• ??????? ???????? ?????? ?????? ????? ?? ????
• ????
• ????? ???? ?? ?? ??????? ??????
• ?????? ????? ????? ?????? ??????

3
????? ????????

• ?????? ??? ?????
• Sailors(sidinteger, snamestring,
  ratinginteger, agereal)
• Reserves(sidinteger, bidinteger, daydates,
  rnamestring)
• ????? Reserves
• ????? 40 ????,
• 100 ?????? ???
• ?-1000 ????
• ????? Sailors
• ????? 50 ????,
• 80 ?????? ???
• ?-500 ????

4
????? ????? ?????

• ????? ???? ??????? ?????? ?A op c(R)
• ???? A ???????? ?? R
• op ??? ??????? ???? gt,
• c ??? ????
• ??????, ????? ???? ?? ??????? ????

SELECT FROM Reserves R WHERE R.agent Joe
5
??? ??????, ??? ???

• ???? ???? ?? ?????? ?? ??? ????? ?? ????
• ?? ???? ?? M ???? ???? ???? ?? ????? M ????
• ?????? ????? ?????, ????? ??? ????? 1000 ????

6
??? ??????, ?? ???

• ?? ???? R ?????? ??? ???????? A ????
• ????? ?? ?????? ??????? ??????? ?? ????? ?? ???
  ????? ??????
• ?????? ?? ??? ??????? ??????? ?? ?????
• ?????
• log(M) pages with tuples from the result

7
????? ??????? ????? ?? B

• ???? ????? ?????? ?? ????? A ?? R ?????? ??? B
• ?????? ????? ?????? ??? ?????? ?? ???? ??????
• ??????? ????? ?? ??? ?? ??? ?????? ??????? ??
  ???? ??????
• ?????? ?? ??????? ???????? ??????

8
?????

• ?????? ????? ?? ????? rnameltC ?? Reserves
• ????? ??????? ????? ????
• 2/26 ? 10 ?10,000 tuples 100 pages
• ?? ??????? ????, ?????? 100 ????? ????? ?????
  ??????? (????? ?????? ???????)
• ?? ??????? ???? ???? ????? ?? 10000 ????? ?????

9
????? ??????? ????? ????? (Hash) ????? ??????

• ????? ???? ?????? ? 2-1 ????? ?????
• ???? ??????? ???????? ???????
• ?? ?? ?????? ????? ?? ???? ?? rname ?-Reserves
  ??? 100 ?????? ????? ?? ??? Joe ?? ????? ??? ????
  rnameJoe ????? ??? 1 ?-102 ????? ????? (??? ??
  ?????)

10
????? ????

• ????? ???? ???? ???? ?? ?????
• ????? ?????????? ?? ??????
• ????? ????????
• ????? ???????? ?? ???
• ????? ?????
• ????? ?????? (hashing)
• ????? ??????? ????? ?? ???? ?? ?????? ?????
  ?????? ????? ?????? ?? ??????

11
????? ?????? ?????
12
?????

• ????????? ???? ?? ??????? ????

SELECT FROM Reserves R, Sailors S WHERE
R.sid S.sid
13
???? ????? ??????

• Simple Nested Loop Join

foreach tuple r of of R do foreach tuple s of S
do if ri rj then add ltr,sgt to result
14
????? ????

• ???? M ?????? ?-R ?-N ?????? ?-S ????? pR ??????
  ????? ?-R ?-pS ?????? ????? ?-S
• ???? ?????? M pRMN
• ?????? ?? ???????
• 1,000 1001,000500 1,000 5107 I/Os
• ????? ?? 10ms ??? ????? ???? ?????? ?????? ?-140
  ???? ?????? ??????!

15
????? ??????? ?????? ?????? ?? ??? ?????

• ????? ??? ?????? ???????? ????? ????? ???????
  ????? ???? ????? ????? ?????
• Block Nested-Loops Join
• Index Nested-Loops Join
• Sort-Merge Join
• Hash-Join
• ????, ???? ?? ?? ??? ?????? ?????? ????

16
????? ?????? ????? ??R(A,B) ?? S(B,C)

• ?? ?? ????? ??????, ??? ?? R ??????? ???????
• ??? ???? ?? S, ??? ?? ????? ??????? ???? ??
  ?????? ?? ??????? ????? ?? ?? ?? ??????? ?? R
• ???? ????? ????? ???? ????? ??? ?? S

17
???? ??????? ??????

• ???? ??????? ?? ???? ???? ??? ?????? 2
• ???? ??? ?????? ???? ????
• ???? ??? ?????? ??????
• ????? ?????, ?????? ???? ????? ???????? ????? ????

18
??? ????? ????

• BR ?????? ?- R ?- BS ?????? ?- S
• ?? ???? ?? R ?- S ???? ??? ???
• ?? ???? ?? ?????? ???? ??? ???
• ??? ???? BR BS output size
• ????? ????? ????? ?????? ??????, ?? ??? ??? ???
  ??????
• ??? ???? ??? BR BS

19
?? ????? ?? ?? ??? ???? ?????? ???? ????????
???????

• ??????? ?????? ?? B ??????
• ??? ?? R ?????? ?? ??? B-2 ??????
• ???? ?? ??? ?? R, ?????? ?? ?? S, ???? ??? ???
  ???
• ??? ?? ?????? ?? ??????? ?????? ?? S ?? ???????
  ????? ?? R ????? ???????
• ?? ceil(BR /(B-2)) ???????? ???? ?????? ?? ?? S,
  ???? ?- R ???? ??? ???
• ???? ????? ??? BR BS ceil(BR /(B-2))

20
????

• ???? ??? BR BS ceil(BR /(B-2))
• ??? ???? ??????? ???????? ???? ???? ???? ?? ????
  ??????
• ????? ???? ???? (?????)

21
Block Nested Loops Join

• Suppose there are B buffer pages

foreach block of B-2 pages of R do foreach page
of S do for all matching in-memory pairs
r, s add ltr,sgt to result
22
????? ????

• ??????? ?-blocked nested loop join ?????? ??????
  ??????? ?????? ??? ????? ?-102 ?????? ?????
  ???????
• ????? ?-Reserves ???? ???????
• ?????? 1,000 ????? ?????? Reserves,
• ??? 100 ?????? ?? Reserves ???? ?? Sailors, ???
  10 ?????? ?? ???? Sailors ?? ??? ?? ????? 500
  ??????
• ?? ??? 1,000 10500 6,000 I/Os

23
????? ????

• ???? Reserves ?????? ???????? ?????
• 1,000 (1,000/100)500 6,000 I/Os
• ???? Sailors ?????? ???????? ??????
• 500 (500/100)1,000 5,500 I/Os
• ????? 10ms ?????? ???? ????? ??? ???? ???? ??????
  ??????!

24
Index Nested Loops Join

• ???? ??? ?????? ?? ???????? ?? S ????? ?????? ??
  S ?? R
• ??????? ??????? ?????? ??????? ????????!

foreach tuple r of R foreach tuple s of S
where risj add ltr,sgt to result
25
????? ????? ????? ??????

• ????? ????? ?? R(A,B) ?? S(B,C) ???? ?? ??????
  ?? ????? B ?? S
• ??? ???? ?? R ???? ????? ??????
• ??? ????? ??????? ?? ?? ??????? ???????? ?? S
• ???? ?????? ?? R ??? ??? ????

26
????? ????

• ?????? ????? ?? R, ???? ?"? X ?? ???? ????? ???
  ????? ?? ?? ??????? ???????? ?? S
• ???? ????? BR tRX (tR ??' ??????? ?- R)

27
??? X?

• ???? ???????? ???? ????? ????? ??????
• (k, rid)
• ?? ?? ?? ???? ????? ????? ?? ???? k ????? ????
  ????? ????? ?? ???? ???, ????? b
• ??????? ????? ????, ??????, ????? 1.2 ?????? ???
  ????? ????? b
• ???????, ???? ?? B, ???? ????? 4-2 ?????? ???
  ????? ????? b

28
???? ?????? ?? X

• ???? ????? ?? ?? ??????? ???? ????? S
• ?? ??????? ????, ???? ????? (????"?) ???? ?? ????
  ??? ?? S ????
• ???? ????? ???? ??? ?? S
• ?? ??????? ???? ????, ?? ?? ????? ?????? ?? S
  ????? ?? ???? ????, ????? ??????? ?? S ?????
  ????? ???? ????? ??????? ?? S ???????? ?????? ???
  ?? R (???? ????? ??? ?????? ???? ??)

29
??????

• ?? ????? ??????? ????? ????, ?? X2.2 ????? ?????
  ???
• BR 2.2tR
• ????? BR BS ceil(BR / (B-2)) ????? ?? Block
  Nested-Loops Join

30
????? ?????

• ???? ??? ?????? ????? ?? sid ?? Sailors
• sid ???? ?? Sailors ?? ?? ??? ????? Reserves
  ????? ????? ?????? ??? ?-Sailors (?????, ?????
  ??? ????)
• ????? Reserves ????? ????? 1,000 ??????
• ?? 1001,000 100,000 ?????? ???? Reserves
• ??? ????? ?????? ??????? ???? ????? ?????? 1.2
  ??????

31
???? ?????

• ?? ????? ??????
• 1,000 100,000 2.2 221,000 I/Os
• ?? ???? ????? ????? ????? ???? ?- 10ms ??? ?-35
  ????

32
???? ?????

• ???? ??? ?????? ????? ?? sid ?? Reserves
• ????? Sailors ????? ????? 500 ??????
• ?? 80500 40,000 ?????? ?-Sailors
• ?? 100,000 ?????? ?-40,000 ?????? ????? ???????
  ????? 2.5 ?????? ?????
• ????? ???????? ???? ????
• 500 40,000(1.2 2.5) 148,500 I/Os
• (?????? ????? ?-25 ????)

33
Sort-Merge Join

• ????? ????? ?? R(A,B) ?? S(B,C) ?"? Sort-Merge
  Join
• ??????? ?"? ??????? ?? B ??? ??????
• ?????? ?? R ?? ?- R.B gt S.B
• ?????? ?? S ?? ?- S.B gt R.B
• ?????? ?? ??? ?????? ??????? ?? ?- R.B S.B, ???
  ?????? ??????? ?? ?? ???? ?? R ??? ???? ?? S
  ?????? ?? B ??????? ?? ?????? ??? ??? ????? ???

34
sid sname rating age
22 dustin 7 45
28 yuppy 9 35
31 lubber 8 55
36 lubber 6 36
44 guppy 5 35
58 rusty 10 35
Sailors
sid bid day agent
28 103 12/4/96 Joe
28 103 11/3/96 Frank
31 101 10/2/96 Joe
31 102 12/7/96 Sam
31 101 13/7/96 Sam
58 103 22/6/96 Frank
Reserves
35
??? ?????? ?? ????? ?"? Sort-Merge Join

• ??? ?????
• BRlogBR BSlogBS
• ??? ?????? (??? ???? ?????)
• BR BS
• ??"?
• BR BS BRlogBR BSlogBS

36
?????

• ???? Reserves ????
• 1000 log 1000 ? 10,000 I/Os
• ???? Sailors ????
• 500 log 500 ? 4,500 I/Os
• ????? 1,000 500 1,500 I/Os
• ??"? 10,000 4,500 1,500 16,000 I/Os
• ???? ????? ???? ???? ????? ????
• ?? ??? ????? ?????? ???? ?????, ???? ?????? ??
  ???? ?????? ????? ???? ????? ??????? ???

37
Hash-Join

• ?????? ????? ?? ??? ???? ???? ?-B-1 ????? ?? ???
  ????? ??????? ????? ?? ?????? ??????????? ??????
  ???? ??????
• ?? ?? ??????, ?????? ?? R ????? ?????? i ??????
  ?????? ?? ??????? ?? S ??? ?? ???? ?-i
• ?????? ?? ?????? ?????? ?? R ??? ??? ?? ??? ?????
  ?????? ??? ?????? (??????? ??????? ?????) ?????
  ??????? ????? ???? (???? ????????!)
• ??? ????? ????? ?? R ?????? ?? ?????? ??????? ??
  S ?????? ????? ??????

38
Hash-Join

• //Partition R into k partitions
• foreach tuple r in R do //flush when fills
• read r and add it to buffer page h(ri)
• foreach tuple s in S do //flush when fills
• read s and add it to buffer page h(sj)
• for l 1..k
• //Build in-memory hash table for Rl using h2
• foreach tuple r in Rl do
• read r and insert into hash table with h2
• foreach tuple s in Sl do
• read s and probe table using h2
• output matching pairs ltr,sgt

39
Hash-Join

• Partition both relations using hash fn h R
  tuples in partition i will only match S tuples in
  partition i

• Read in a partition of R, hash it using h2 (ltgt
  h!). Scan matching partition of S, search for
  matches

40
Partition R S using h
41
Read partition Ri partition it using h2
Partition Si is joined with Ri
42
????? ?? Hash Join

• ?? ?? ????? ???? ??????? ??- Partitions ?? ????
  ?? ???? ????? ????, ??
• ???? ?????? ?????? ??????? ?? ??? ??? ??? ????
• ????? ???? ?????? ?? ??? ??? ???
• ??? ????? ????? ??? 3(BR BS)

43
?????? ???????

• ???? 1,000 ?????? ?? Reserves ?-500 ?????? ??
  Sailors ??????
• 3(1,000 500) 4,500 I/Os
• ????? ??, ?????? ?????? ?????? ?-45 ?????

44
???? ??????? ?????? ?????? Hash Join

• ?? ?? B ?????? ??????? ?? ???? ?- Partitions ???
  ??? ????? kB-1
• ??? ????? ?? ?? Partition ???
• ??? ???? ???? ???? ?????? ??? Partition, ???? f
  ??? ???? ???? ?- 1, ?? ?????? ???? 100 ?????

45
???? ????? ???? ??????? ?????? ?????? Hash Join

• ?????, ???? ???? ???? ??????
• ?? ??? ??? B ?????? ????? ?? ??? ?????? ??????
  ???? ?????? ???? ????, ????
• ?????, ???? ??????, ???? R ???? ????
  ????

????? ???? ??? ????, ?? ?- Partitions ?? ?????
????
46
???? ?????? ?-Sort-Merge Join (SMJ)

• ?? ???? ?????? ?- SMJ ?????? ???? ??? ??? hash
  join 3(BR BS)
• ???? ??? ????? ?????? ???????, ???? S ???
  ????? ???? ?????? (????? ???? 12)
• SMJ ?????? ????? ?????? ???? ?????? ??? worst case

47
????? ?? ??

• ????? ????? ????? ?????? ????? ?? ??? ?????
• ??? ???? ????? ???? ??? ??????
• ??? ???? ???? ???? ??? ????