Join Algorithms

1
Join Algorithms For Online Aggregation
Peter J. Hass Joseph M.
Hellerstein IBM Research Division
Computer Science Division

University of California
Presented By Michal Ozery
2
????????? Ripple Join

• ???? ?????? ???????
• ?????? ?? ???? ?????? ???? ??????
• ????? ???????? ????? ?? ?????? (SUM, COUNT,
  AVG,..)
• ????? Online

3
????????

• ?????? ?????? ????? ?????? ????????????.
• ??????? ?? ?????? ?????? ????.
• ????? ???? ??? ???? ????? ????? ???? ?????
  ????? ??????.

4
?????? ??? ????????? Join ?????? ??????????
Ripple Join
Ripple Join
Join ????

• ????? ????? ???? ?????? X-h, Xh (????????
  ??????) ???????? p (????? ????).
• ??? ?????? ?????? ????? ?????. (????? ????????
  ???????)
• ???? ????? ??? ????? ?? ??????? ?? ????? ?????
  ??????.

• ????? ?????? ??????? ?? ??? ???????.
• ??? - ????? ?????.
• ???? ????? ??? ????? ????? ?? ???????.

5
?????? ??? ????????? Join ?????? ??????????
Ripple Join (????)
Join ????
Ripple Join

• ??? ???? - ???? ???? ????? ?????? ?????.
• ???? ?????? ????? ????? ???????, ????? ???????
  ?????????? ?? ???????. (?????, ?????, )

• ??? ???? - ???? ????? ?????? ?????.
• ???? ?????? (plan) ???? ????.

6
????
????? ??????? (tupples) ??????? ???? ?????
???????? ????? K ?????? ??????? ???? K ??????
???? ?-
(??????? ?????)
7
?????????? ??????
???????? Nested Loops. (????? ?????? COUNT) for
I1 1 to R1 for I2 1 to R2
. for IK 1 to RK sum
Join_Condition(R1I1, ... RKIK) ???? ??
????? ?- R1 - ??????? ?? ?????? ??? ????????
???????.
8
??????? ?????? ?- Nested Loops (?)
???? R, S ??? ??????. R gt S. ??? S ????
?????? ????????.

• ??? ??? ????? ???? ????? ?? ?? R gt ????
  ??? ???????? ???? ????? ????!
• ????? ?? ???? ????????? ?? ???? ????? ??? ???
  ????? .
  ????? ?? ???????? ??????? ?? ?-Join ??????
  ????? ????? S.

9
??????? ?????? ?- Nested Loops (?)
10
Ripple Join - ????? ???????
R ?- S ??? ??????. ??? ??? ?????? ?? ?- S ???
?-R ??????? ?? ??? ??????? ?? ?? ??????? ???????.
gt ???? ????? ?????? ??????? ??? ?????
????????. (????? ??? Rectangles of Increasing
Perimeter Length)
11
???? ????? ?????
12
????????? ?? Ripple Join

• Block Ripple Join ?????? ?????? ?? ?????? ???
  ?????? ??????. (????? ?- I/O).
• Hash Ripple Join - ?? ?????? ?????? ?? ?? ??????
  ??????? Hash ???????. (equi-join)
• Index Ripple Join ?? ?- Ri ?? ?????? ?? ??????
  ?- Join - ????? ????? ?? ?? ????? ???? ???
  ?????. gt ???? .
  (???? ?- Nested Loops ?? Index.)

13
???????? ??????

• ?????????? - ???? ?? X ???????? gt p.
• Large Sample - ???? ?? X ???????? p. ?????
  ?? ???? ????? ?????? (Central Limit Theorem).

14
???????? ?????? - ????
CLT - ?? ???? n ?????? ?? ???? ??????? ???
??????? ????? ?????? ???????? ???????.
?????, ?? Xi ?? ???? ????? E ??????
?????
??? -
???????
15
Ripple Join ???? COUNT (?)
????? ??????? ?????
?? ?????? ???? ?- Join. ????.
????? ????? ???? X f. ?????? f ??????????
?????? ?? ????? ???? ?- ???
????? ???????.
????? ?? ??? ????? ??? ???? ???? ?? Ef ?? ?????
?????? ?? ????? ???????. ???? ?????? ?? Tn.
16
Ripple Join ???? COUNT (?)
???? ????
???? ?????
17
Ripple Join ???? COUNT (?)
18
Ripple Join ???? COUNT (?)
??
???? ????? ??? ??? ????? ???? ??????
??????? Rk ???? ???.
??? ?? ???? ????
19
Ripple Join ???? ???????? ?????
20
????? ???? ?????? (?)
?????? ???? ??? ????? gt ???? ????? ???? ?????
????? ???????? ??? ????? ??? ?????? ?? ????? ????
?????? ?????.
21
????? ???? ?????? (?)
22
????? ???? ?????? (?)
?????? ???? ?????? ?-
?????? ??? ??? gt ????
?????? ??????????? ????? ?? ?? gt
???? ????? ??????? ??????? ???? ???????
?????????? ?? ???????.
(!) ?? ?????? ?? ???? ?????? (????? ???????
????????)
23
???????

• ???? ?????? PostgreSQL.
• ??? ?????? student - ????? ??? ????? (60,300)
  enroll - ????? ??? ??????? ????? ?????
  (1,547,606)

SELECT student.honors_code, AVG(last.grade) FROM
enroll, student WHERE enroll.sid
student.sid GROUP BY student.honors_code
24
?????? ??? ??????? ?????????
????????? ????? ?? ?- Optimizer ?? ?- 446 ????? !
25
??? ????? ??????.
26
????? ???? ????? ??????? (?).
SELECT ONLINE AVG(later.grade) FROM enroll
first, enroll later WHERE first.sid
later.sid AND first.crseid later.crseid AND late
r.passfail t AND first.year lt later.year
Hash Ripple Join ???? ?? ????? ???? ???? ?????.
27
????? ???? ????? ??????? (?).
????????? ??- Optimizer ??? ?? ?- 25 ????!
28
??? ???.
????? ????? ?????????? ??? Ripple Joins

• ??? ????? ?????? ?????? ??????? ????? ?? ????
  ??????.
• ??? ????? ??????.
• ??? ???? ????? ???????.
• ????? ??????????? (?????? ???? ?? ??? ?????
  ???????)

THE END!